Business Hours reporting (Web-based)

Hello chaps,

Anyone been able to generate a report using business hours i.e. 0800 to 1730 for the time period? I am looking to use this for interface utilization.

With the removal of the Report Writer I have not been able to find an alternative as of yet using the Web Based report writer.

All the best

Find more posts tagged with

network_monitoring

report_writer

network_management

Reporting

npm

Accepted answers

blsanner

War Eagle!!! I can't wait for football season to start!

Below is a SQL query that should work for what you are looking for. It will give you the average availability of all of your nodes over the last 30 days between 8:00 AM and 5:00 PM, taking into account the row weights in the ResponseTime view. You would use this in a web report by adding a custom table, choose "Advanced Database Selection (SWQL/SQL)" as your Selection Method and SQL and your query type. Finally, choose the columns Node Name and Availability in your table.

SELECT Caption AS 'Node Name',

CONVERT (varchar,round(AVG(MA.SumProduct / MA.SumWeights),3)) AS Availability

FROM (

SELECT RT.NodeID,

SUM(RT.Availability * RT.Weight) AS SumProduct,

SUM(RT.Weight) AS SumWeights

FROM ResponseTime RT

JOIN Nodes N ON RT.NodeID = N.NodeID

WHERE DateTime > DATEADD(day, -30, GETDATE())

AND DATEPART(weekday, DateTime) >= 2

AND DATEPART(weekday, DateTime) <= 6

AND DATEPART(hour, DateTime) >= 8

AND DATEPART(hour, DateTime) <= 16

GROUP BY RT.NodeID

) MA

JOIN Nodes N ON MA.NodeID = N.NodeID

GROUP BY Caption

All comments

JiriPsota

The only way now is to use SQL or SWQL as Datasource for Custom table or Custom chart resource.

jcauburn

Have you found any resources on how to complete this? I have been looking for a framework on how that is set up. I downloaded the SDK and have looked a bit on the forums, I can't seem to find where the availability is set up in the database, or how to calculate it.

blsanner

Availability is located in the ResponseTime view as well as in the ResponseTime_Daily, ResponseTime_Detail, ResponseTime_Hourly, and ResponseTime_Statistics tables in the database. If you are just looking for availability within your detail statistics retention window (default 7 days), you can use the ResponseTime_Detail table. Otherwise, I would recommend using the ResponseTime view. The only caveat is that you will have account for the weight of each row instead of simply doing an average. The Weight column is derived from whether the statistic is a detail, hourly, or daily value.

jcauburn

Thanks for the help, and War Eagle! If you are willing to give a little more of a primer, I see the Node link in that table back to Orion.Nodes. How would the query go to use that link? I tried

SELECT Node from Orion.ResponseTime

but it came back with the error

Data type Orion.Nodes not found.

If you could point me towards some resources where you learned that information, I would greatly appreciate it.

mharvey

Report Writer hasn't been completely removed, so you can still use that as an option. From the web, you'd need to use a Custom Table report, and use Custom SQL in order to generate a report filtered by business hours. It would look something like:

Select

Nodes.Caption as Node,

ResponseTime.Availability as Availability

From Nodes

JOIN ResponseTime on Nodes.NodeID = ResponseTime.NodeID

Where

DateTime >= DATEADD(DAY, -30, GETDATE())

AND

(

(DATEPART(weekday, DateTime) >= 2) OR

(DATEPART(weekday, DateTime) <= 6) OR

(Convert(Char,DateTime,108) > '08:00') OR

(Convert(Char,DateTime,108) <= '17:00')

)

This would return the availability of all nodes over the last 30 days M - F from 8am to 5pm.

Regards,

Matthew Harvey

Senior Sales Engineer

Loop1 Systems

http://www.loop1systems.com

Putting Knowledge Into Motion

FormerMember

Hi,

Just wondering why are you ORing the times & days conditions inside the WHERE statement?

Isn't it suppose to be ANDs?