HI,
I was trying to create an APM component report for a month , with daily summarisation.
For example - I want '%committer bytes' (APM component) statistics plotted as each day's average value for the last month (so that we can see the trend- whether its going up or down), for each server.
Whatever option I select in report writer, I was not able get this report.
Please find the SQL queries for the closest report I got. In this report, its not able to give average value for a day. Its just giving 5 mins values for that component for a day. Thats a huge lot of data.
-------------------------------------------------------------------
SELECT TOP 4000
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
APM_AlertsAndReportsData.ComponentName AS Component_Name,
APM_ResponseTime.StatisticData AS StatisticsData,
APM_ResponseTime.DateTime AS DateTime
FROM
(Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId)) INNER JOIN APM_ResponseTime ON (APM_AlertsAndReportsData.ComponentId = APM_ResponseTime.ComponentID)
WHERE
( DateTime BETWEEN 40846 AND 40875.9999884259 )
AND
(
(APM_AlertsAndReportsData.ComponentName = '% Committed Bytes In Use')
)
ORDER BY 5 ASC
--------------------------------------------------------
OUTPUT is below
| Node | Event Type Name | StatisticsData | Date Time |
| NGMWAN01 | % Committed Bytes In Use | 11 | 30-Nov-11 11:59 PM |
| 30-Nov-11 11:53 PM |
| 30-Nov-11 11:48 PM |
| 30-Nov-11 11:43 PM |
| 30-Nov-11 11:38 PM |
| 30-Nov-11 11:33 PM |
| 30-Nov-11 11:28 PM |
| 30-Nov-11 11:23 PM |
| 30-Nov-11 11:18 PM |
| 30-Nov-11 11:12 PM |
| 30-Nov-11 11:07 PM |
| 10 | 30-Nov-11 11:02 PM |
| 11 | 30-Nov-11 10:57 PM |
| 30-Nov-11 10:52 PM |
| 30-Nov-11 10:47 PM |
| 30-Nov-11 10:42 PM |
| 10 | 30-Nov-11 10:36 PM |
| 11 | 30-Nov-11 10:31 PM |
But I want only one value for statistics data ( average for that day) for each day.
Something like below - where statistics data should be the average of all values for that day.
Test-Committed Bytes
NodeEvent Type NameStatisticsDataDate TimeNGMWAN01% Committed Bytes In Use12.946996466431101-Nov-1112.477707006369402-Nov-1111.354978354978403-Nov-1111.560165975103704-Nov-1110.626609442060105-Nov-111006-Nov-117.9917012448132807-Nov-118.8032786885245908-Nov-11
Could some one help me on this with correct SQL query?
