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?
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=15introscope.enterprisemanager.smartstor.tier1.age=7 --> 30
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_dataWHERE 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)
Can anyone make further suggestions for Aris?