Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Need to get local Business hours reports

My SolarWinds polling engine is in the central time zone of the U.S., but I am monitoring devices all over the world. My challenge is that I need to run weekly reports covering LOCAL business hours (8AM to 6PM). In other words, a report for devices in Paris should include data from 8A to 6P in Paris, not 8A to 6P in Dallas.

How do I do that in SQL? Can it be done directly with Manage Reports?

Thank you for any guidance as I am not a SQL expert.

Find more posts tagged with

Accepted answers

All comments

mrxinu

Hey patriot - long time no chat!

The way I handle this is by creating a floating point node custom property with the GMT offset for each node (e.g., -7 for PDT, -5 for CDT, and -4 for EDT). Then when I create the report, I limit the rows (in SWQL or SQL) to those where the hour is >= (dateTimeField + customPropertyUTCOffsetField) if the dateTimeField is UTC already (as it is with availability). Boom, you have a timezone aware report! If you want a hand with the particulars, give me a shout and we can hop on a GoToMeeting and chat about it.

uaggarwal

can you check with the below query, if the query is getting executed directly on the database, i am able to get the output of the query, whereas if running on the application, the data is not getting fetched. :

SELECT TOP 10

CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth,

Nodes.Caption AS NodeName,

Interfaces.InterfaceName AS Interface_Name,

AVG(Case InBandwidth

When 0 Then 0

Else (In_Averagebps/InBandwidth) * 100

End) AS AVERAGE_of_Recv_Percent_Utilization,

AVG(Case OutBandwidth

When 0 Then 0

Else (Out_Averagebps/OutBandwidth) * 100

End) AS AVERAGE_of_Xmit_Percent_Utilization,

Nodes.Region AS Region,

InterfaceTraffic.DateTime AS DateTime,

Interfaces.InterfaceID AS InterfaceID

FROM

(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE

( DateTime BETWEEN 43219 AND 43249.9999884259 )

AND

(

(Nodes.Region LIKE '%Networks%') AND

(DatePart(Hour,DateTime) >= 9) AND

(DatePart(Hour,DateTime) <= 18) AND

(Nodes.Caption = 'reckben-ingnpip01-2953349') AND

(DATEPART(weekday, DateTime) <> 1) AND

(DATEPART(weekday, DateTime) <> 7)

)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

Nodes.Caption, Interfaces.InterfaceName, Nodes.Region, InterfaceTraffic.DateTime, Interfaces.InterfaceID

ORDER BY SummaryMonth ASC

reckben-ingnpip01-2953349

mesverrum

When you say it is not getting fetched, is there an error message, does the report time out?

uaggarwal

The application goes in hung state.