DX Application Performance Management

Expand all | Collapse all

CEM Historical Data?

Jump to Best Answer
  • 1.  CEM Historical Data?

    Posted 03-01-2013 09:33 AM
    Hi,

    I must have unchecked (unintentionally) some settings for me to get here but I don't know how to get back. Need some help.

    From CEM > Analysis Graphs tab, I can only view data for today's transactions but if I change the 'Timeframe' dropdown to yesterday or any criterion, there is no data. What is needed to get historical data.

    I'm on v9140.


  • 2.  RE: CEM Historical Data?

    Posted 03-04-2013 01:35 AM
    Hi ,

    One of the possible cause is that, the daily aggregation services may not be happening. Could you check the EM logs (if cluster ,check the EM which does the aggregation service).
    I faced this issue twice. Support provided me with a set of update queries which would resume the aggregation service.

    My issue was, CEM tried to do daily aggregation from a particular date but failed to find the partition table of that date. So we modified the APM DB tables by updating the last successful aggregation date.

    Geethu


  • 3.  RE: CEM Historical Data?
    Best Answer

    Posted 03-04-2013 09:18 AM
    @geethu_john, Thank you for the heads up. And yes, Stats Aggregation Service is not working. Can you give me your support ticket number so I can reference it in my support ticket? I already had a support ticket opened but it appears the support tech is having hard time diagnosing my issue.

    To view your closed tickets, go to Support > View Cases > select "Closed" for Status > click GO

    Your help is appreicated.


  • 4.  RE: CEM Historical Data?

    Posted 03-04-2013 11:58 AM
    @VLu,

    Just in case you are seeing a similar error in your logs, here is something I wrote up that sounds simlar to what you're experiencing; I've encorporated several CA Support tickets worth of feedback into it (from tickets 20823288-1: CEMDB ERRORS, 20913091-1: CEMDB ERRORS (AGAIN), and 21026019-1: CEMDB RELATION DOES NOT EXIST). This only applies if you see a similar error, however, and [color=#ff0000]I would confirm with CA Support before following the resolution steps[color].

    ERRORS:
    relation "ts_st_ts_us_int_YYYYMMDD" does not exist
    relation "ts_st_ts_us_dly_YYYYMMDD" does not exist
    relation "ts_st_tu_us_int_YYYYMMDD" does not exist

    Summary
    In the [font=Courier New]IntroscopeEnterpriseManager.log[font] of the CEM Collector (or equivalent), you seen an error like the below (exact table name may be different):
    6/20/12 12:02:49.610 AM PDT [ERROR] [Thread-815] [Manager.com.timestock.tess.services.processors.StatsAggregator] [main] [com.timestock.tess.util.TessLogger] Select failed for ts_st_ts_us_int_20120523
    org.postgresql.util.PSQLException: ERROR: relation "ts_st_ts_us_int_20120523" does not exist
    6/20/12 12:02:49.612 AM PDT [ERROR] [Thread-815] [Manager.com.timestock.tess.services.processors.StatsAggregator] [main] [com.timestock.tess.util.MainProgram] Unexpected exception in daily aggregation
    com.timestock.common.exceptions.TsUnexpectedDatabaseErrorException: Unexpected database error: could not execute query

    at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4447)

    at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4382)

    at com.timestock.tess.util.StatisticsAggregation.getLastStats(StatisticsAggregation.java:828)

    at com.timestock.tess.util.StatisticsAggregation.getDatabaseRows(StatisticsAggregation.java:655)

    at com.timestock.tess.util.StatisticsAggregation.getDatabaseRows(StatisticsAggregation.java:613)

    at com.timestock.tess.util.StatisticsAggregation.getStatsForPeriod(StatisticsAggregation.java:501)

    at com.timestock.tess.util.StatisticsAggregation.getStatsForInterval(StatisticsAggregation.java:476)

    at com.timestock.tess.services.processors.StatsProcessingBase.processMissingStats(StatsProcessingBase.java:389)

    at com.timestock.tess.services.processors.StatsAggregator.doDailyAggregations(StatsAggregator.java:689)

    at com.timestock.tess.services.processors.StatsAggregator.doDailyAggregations(StatsAggregator.java:601)

    at com.timestock.tess.util.MainProgram.doStatsAggregation(MainProgram.java:98)

    at com.timestock.tess.util.MainProgram.run(MainProgram.java:50)

    at com.timestock.tess.util.MainProgram.main(MainProgram.java:41)
    Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

    at org.hibernate.loader.Loader.doList(Loader.java:2214)

    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)

    at org.hibernate.loader.Loader.list(Loader.java:2090)

    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)

    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)

    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)

    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)

    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)

    at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4414)

    ... 12 more
    Caused by: org.postgresql.util.PSQLException: ERROR: relation "ts_st_ts_us_int_20120523" does not exist
    Resolution
    1. Make note of which table(s) is impacted; transet (ts_st_ts...), transgrp (ts_st_tsgrp...), transunit (ts_st_tu...). The rest of the steps will assume it occurs at the transet level, but it could be any or multiple of the three. This problem normally occurs at the transet level, but could also be occuring at the transunit. The errors for transet could mask the similar error for transunit; therefore, upon resolving one, you may still encouter the other. Be sure to check!

    2. Shut down the CEM Collector EM.

    3. Log into the CEMDB either remotely via DBVisualizer (or similar) or locally using the pgAdmin III utility.

    4. To check when the last transset data collection was made, run the following query. This will help you know just how long you've had the problem and possibly change how you resolve it.
    [font=Courier New] select 'ts_st_ts_all_dly', max(ts_interval_start_time) from ts_st_ts_all_dly
    union all select 'ts_st_ts_all_int', max(ts_interval_start_time) from ts_st_ts_all_int
    union all select 'ts_st_ts_all_mly', max(ts_interval_start_time) from ts_st_ts_all_mly
    union all select 'ts_st_ts_all_wly', max(ts_interval_start_time) from ts_st_ts_all_wly
    union all select 'ts_st_ts_us_int', max(ts_interval_start_time) from ts_st_ts_us_int
    union all select 'ts_st_ts_us_dly', max(ts_interval_start_time) from ts_st_ts_us_dly
    union all select 'ts_st_ts_us_mly', max(ts_interval_start_time) from ts_st_ts_us_mly
    union all select 'ts_st_ts_us_wly', max(ts_interval_start_time) from ts_st_ts_us_wly
    union all select 'ts_st_ts_usgrp_dly', max(ts_interval_start_time) from ts_st_ts_usgrp_dly
    union all select 'ts_st_ts_usgrp_int', max(ts_interval_start_time) from ts_st_ts_usgrp_int
    union all select 'ts_st_ts_usgrp_mly', max(ts_interval_start_time) from ts_st_ts_usgrp_mly
    union all select 'ts_st_ts_usgrp_wly', max(ts_interval_start_time) from ts_st_ts_usgrp_wly[font]
    5. If the date in the error message (or in the above queries) is a recent date, perform substep A. If the date is very old, perform substep B. You could try adjusting either query set based on the findings from Step 4.
    A. Edit the following SQL statement to that the “INTERVAL ‘8 hour’” string represents the correct amount of time between now and the midnight past, then execute the query.
    [font=Courier New]UPDATE ts_st_ts_all_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_all_int SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_all_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_all_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_us_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_us_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_us_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_usgrp_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_usgrp_int SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_usgrp_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
    UPDATE ts_st_ts_usgrp_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';[font]
    B. Clean out the transset data , run the following queries. This will purge the bad data entirely, ensuring a fix but removing stored data. If you don't have recent data, this might be OK.
    [font=Courier New]DELETE FROM ts_st_ts_all_dly;
    DELETE FROM ts_st_ts_all_int;
    DELETE FROM ts_st_ts_all_mly;
    DELETE FROM ts_st_ts_all_wly;
    DELETE FROM ts_st_ts_us_int;
    DELETE FROM ts_st_ts_us_dly;
    DELETE FROM ts_st_ts_us_mly;
    DELETE FROM ts_st_ts_us_wly;
    DELETE FROM ts_st_ts_usgrp_dly;
    DELETE FROM ts_st_ts_usgrp_int;
    DELETE FROM ts_st_ts_usgrp_mly;
    DELETE FROM ts_st_ts_usgrp_wly;[font]
    6. Start the CEM Collector EM.


  • 5.  RE: CEM Historical Data?

    Posted 03-04-2013 02:30 PM
      |   view attached
    @jakbulter,

    Thanks and very interesting. I don't see the error you mentioned in IntroscopeEnterpriseManager.log. It "appears" as though the daily aggregation started @ 12:02 am with a warning. Not sure how is the impact of it.

    I searched IntroscopeEnterpriseManager.log using the daily and do not exist keyword and below is what I see. Should there be something like "daily aggregation starts........." and/or "daily aggregation ends........."?
    3/03/13 12:02:00.045 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_ts_all_dly_20130304
    3/03/13 12:02:00.190 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_ts_all_dly_20130304 created
    3/03/13 12:02:00.578 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_ts_us_dly_20130304
    3/03/13 12:02:00.913 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_ts_us_dly_20130304 created
    3/03/13 12:02:01.139 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_ts_usgrp_dly_20130304
    3/03/13 12:02:01.225 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_ts_usgrp_dly_20130304 created
    3/03/13 12:02:01.290 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tsgrp_all_dly_20130304
    3/03/13 12:02:01.335 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tsgrp_all_dly_20130304 created
    3/03/13 12:02:01.393 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tsgrp_us_dly_20130304
    3/03/13 12:02:01.506 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tsgrp_us_dly_20130304 created
    3/03/13 12:02:01.568 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tsgrp_usgrp_dly_2013030
    4
    3/03/13 12:02:01.645 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tsgrp_usgrp_dly_20130304 created
    3/03/13 12:02:01.702 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tu_all_dly_20130304
    3/03/13 12:02:01.790 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tu_all_dly_20130304 created
    3/03/13 12:02:01.842 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tu_us_dly_20130304
    3/03/13 12:02:01.920 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tu_us_dly_20130304 created
    3/03/13 12:02:02.035 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Creating stats partition ts_st_tu_usgrp_dly_20130304
    3/03/13 12:02:02.119 AM EST [INFO] [DailyAggregation.Thread1] [Manager.com.timestock.tess.util.DbUtils] Stats partition ts_st_tu_usgrp_dly_20130304 created
    [color=#f90000]3/03/13 12:02:02.173 AM EST [WARN] [DailyAggregation.Thread1] [Manager.com.timestock.tess.services.processors.StatsAggregator] Bad rows processed: ''[color]

    I also executed the query you posted and looks like the isse started in Janurary. Still waiting for CA to repspond........


  • 6.  RE: CEM Historical Data?

    Posted 03-04-2013 03:21 PM
    @VLu,

    Hmm. The fact that you're getting a [font=Courier New]null[font] timestamp for the [font=Courier New]ts_st_ts_us_dly[font] table when you clearly have daily transaction set processing enabled is worrisome. I'd share that query and the results with CA Support.

    Just a guess, but I'd wager that your "Bad rows" being processed are in that table. Here's what it looks like when I run that same query:
    table_name            max_ts_interval_start_time
    ts_st_ts_all_dly      2013-03-03 00:00:00.0
    ts_st_ts_all_int      2013-03-04 07:00:00.0
    ts_st_ts_all_mly      2013-03-01 00:00:00.0
    ts_st_ts_all_wly      2013-02-25 00:00:00.0
    ts_st_ts_us_int       2013-03-04 07:00:00.0
    ts_st_ts_us_dly       2013-03-03 00:00:00.0
    ts_st_ts_us_mly       2013-03-01 00:00:00.0
    ts_st_ts_us_wly       2013-02-25 00:00:00.0
    ts_st_ts_usgrp_dly    2013-03-03 00:00:00.0
    ts_st_ts_usgrp_int    2013-03-04 07:00:00.0
    ts_st_ts_usgrp_mly    2013-03-01 00:00:00.0
    ts_st_ts_usgrp_wly    2013-02-25 00:00:00.0
    Putting your log file in DEBUG might help get you more info. If you don't wan to set the complete log to DEBUG, adding the below property to the [font=Courier New]IntroscopeEnterpriseManager.profile[font] should do the trick:

    [font=Courier New]log4j.logger.DailyAggregation.Thread1=DEBUG, logfile[font]

    - Jack


  • 7.  RE: CEM Historical Data?

    Posted 03-05-2013 12:15 AM
    Hi VLu,

    Please not the case id:

    21068904 01 - PARTITION NOT EXISTING


    Geethu


  • 8.  RE: CEM Historical Data?

    Posted 03-05-2013 10:43 AM
      |   view attached
    Got a respond for Support Tech and she's asking me to upload the MOM's logs. Wonder where this gonna take me.....saga continues with ca support.

    @Geethu, thank you for the case number.

    @Jack, so I ran the query against these 3 tables (ts_st_ts...; ts_st_tsgrp...; ts_st_tu...). ts_st_tu tables show up blank. Do you have any data on your side?
    select 'ts_st_tu_all_dly', max(ts_interval_start_time) from ts_st_tu_all_dly
    union all select 'ts_st_tu_all_int', max(ts_interval_start_time) from ts_st_tu_all_int
    union all select 'ts_st_tu_all_mly', max(ts_interval_start_time) from ts_st_tu_all_mly
    union all select 'ts_st_tu_all_wly', max(ts_interval_start_time) from ts_st_tu_all_wly
    union all select 'ts_st_tu_us_int', max(ts_interval_start_time) from ts_st_tu_us_int
    union all select 'ts_st_tu_us_dly', max(ts_interval_start_time) from ts_st_tu_us_dly
    union all select 'ts_st_tu_us_mly', max(ts_interval_start_time) from ts_st_tu_us_mly
    union all select 'ts_st_tu_us_wly', max(ts_interval_start_time) from ts_st_tu_us_wly
    union all select 'ts_st_tu_usgrp_dly', max(ts_interval_start_time) from ts_st_tu_usgrp_dly
    union all select 'ts_st_tu_usgrp_int', max(ts_interval_start_time) from ts_st_tu_usgrp_int
    union all select 'ts_st_tu_usgrp_mly', max(ts_interval_start_time) from ts_st_tu_usgrp_mly
    union all select 'ts_st_tu_usgrp_wly', max(ts_interval_start_time) from ts_st_tu_usgrp_wly


  • 9.  RE: CEM Historical Data?

    Posted 03-05-2013 11:47 AM
    @VLu,

    For most of the cemdbs that I manage, the ts_st_tu tables also show up blank.

    - Jack


  • 10.  Re: CEM Historical Data?

    Posted 04-22-2015 05:15 AM
      |   view attached

    Dear VLu et all:

    I created the following Tech Note which later became a KB on this topic.This should help in many situations

     

    Thanks

    Hal German



  • 11.  Re: CEM Historical Data?

    Posted 04-22-2015 05:16 AM

    The Tech Note talks about Bad Rows as well.