Group by custom attribute and report statistics - Report Writer

Question

Hi all,

We have some custom attributes set in the custom property editor, we have our core network nodes listed under the custom property of devicetype called CORE, our voice devices under VOICE, adsl routers under ADSL. You can see where this is going. Wev'e done this so we can group our devices on the NPM summary page. No problem.

We now want to create a report that lists the availability of those grouped nodes, I can obviously report on individual nodes, but not the average of all the nodes in a group. I'd like to produce a report that shows the average availability of all the nodes under that custom header, CORE, VOICE, ADSL, UPS etc. Not sure how to group them.
I would also like to show the average response time, traffic etc based on all the nodes in that custom group.

Any ideas.

scottd · Accepted Answer

Mr. Monkey,

If i understand you correctly, i believe this can be done with an Advanced SQL Report.

Below is a query for you to use as a template. My custom property for this query is Application and the values i am using are OMS and SAP, and the time frame i used was for 7 days(weekly report).

Select(Select 'Availability - ' + CAST ((
Select All AVG(ResponseTime.Availability)
FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE( DateTime BETWEEN (Select DATEADD(DD,DATEDIFF(dd,0,GETDATE()),-7)) AND GETDATE() )AND
(Nodes.Application = 'OMS')
)AS varchar(15)) + '%' + CHAR(13) + 'Avg Response Time - ' + CAST((
SELECT All AVG(ResponseTime.AvgResponseTime)
FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE( DateTime BETWEEN (Select DATEADD(DD,DATEDIFF(dd,0,GETDATE()),-7)) AND GETDATE() )AND
(Nodes.Application = 'OMS')
)AS varchar(15))+ 'ms')AS OMS_Weekly_Stats,

(Select 'Availability - ' + CAST ((
Select All AVG(ResponseTime.Availability)
FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE( DateTime BETWEEN (Select DATEADD(DD,DATEDIFF(dd,0,GETDATE()),-7)) AND GETDATE() )AND
(Nodes.Application = 'SAP')
)AS varchar(15)) + '%' + CHAR(13) + 'Avg Response Time - ' + CAST((
SELECT All AVG(ResponseTime.AvgResponseTime)
FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE( DateTime BETWEEN (Select DATEADD(DD,DATEDIFF(dd,0,GETDATE()),-7)) AND GETDATE() )AND
(Nodes.Application = 'SAP')
)AS varchar(15))+ 'ms')AS SAP_Weekly_Stats

The above query will produce this;

Give it a try and see if it works for you...

Group by custom attribute and report statistics - Report Writer

Top Replies