DX Application Performance Management

Expand all | Collapse all

CA APMSQL group seconds to minutes or hours

Jump to Best Answer
  • 1.  CA APMSQL group seconds to minutes or hours

    Posted 10-08-2018 12:43 AM
      |   view attached

    Hi, our introscope em apmsql only stores 15 secs for 7 days. Is there a command to query or group the ts and value to minutes or even hours?



  • 2.  Re: CA APMSQL group seconds to minutes or hours
    Best Answer

    Posted 10-08-2018 09:32 AM

    ArisLawrence

    With CA APMSQL, an analyst can connect to SmartStor as if it were a relational database. Connections are made using a JDBC driver, or an appropriate ODBC driver for connections to tools like Excel.

    You have to store 15 sec of data from more then 7 days to get your desired result. For this you have to update SmartStor setting in IntroscopeEnterpriseManager.properties file.

    following setting is to save 15 sec of metrics for 7 days. If you want to save 15 sec metrics for 30 days you have to change tier1.age value to 30 or as your requirement.

    introscope.enterprisemanager.smartstor.tier1.frequency=15
    introscope.enterprisemanager.smartstor.tier1.age=7 --> 30



  • 3.  Re: CA APMSQL group seconds to minutes or hours

    Posted 10-09-2018 01:36 AM

    I already have done that. The customer(s) also want to gather data for 30/45 secs or 1 min and 15/30/45 secs or 2 min 15/30/45 secs ... Since smartstor can only use three tiers, i am having a hard time grouping the time. I have a query below but the limitation was it  only queries per minute. The old Introscope JDBC (Using the JDBC API - CA Application Performance Management - 9.7 - CA Technologies Documentation ) has a "period" column where user can input if you want data in 15/30/45 secs and even 1:15/1:30/1:45 minutes.

     

     

    SELECT agent_host,
    agent_name,
    EXTRACT(MONTH from ts) as "Month", EXTRACT(DAY from ts) as "Day",
    EXTRACT(HOUR from ts) as "Hour", EXTRACT(MINUTE from ts) as "Minute",
    sum(value_count) as "value_count"
    FROM numerical_metric_data
    WHERE metric_path = 'Servlets:Average Response Time (ms)'
    AND agent_host != 'Custom Metric Host (Virtual)'
    AND ts between Timestampadd(sql_tsi_day, -12, Now()) AND Now()
    GROUP BY agent_host,
    agent_name,
    EXTRACT(MONTH from ts),
    EXTRACT(DAY from ts),
    EXTRACT(HOUR from ts),
    EXTRACT(MINUTE from ts)



  • 4.  Re: CA APMSQL group seconds to minutes or hours

    Posted 10-09-2018 11:19 AM

    Can anyone make further suggestions for Aris?