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
Store

Defining Times in Reports other than the Pre-programmed times

mhh351

I ahve just written a report for a wide range of interfaces (by Device). This report is filtering on "Last Month".

What I also need to report is by the week (week 1,2,3,4 of the month.) and also the last 2, 3 or 4 days of the month.

This would need to run every month without any intervention for a month change.

The weekly could be a summary of all days within that week. The last "days" report should show each day.

How can I customize my SQL query to reflect those needs?

Thank you for your help.

Mark

Find more posts tagged with

reports_in_npm

Accepted answers

All comments

mcbridea

Looks like what you need are some datetime qualifiers for the query. I believe the only way you will be able to do this is by inserting the proper qualifiers into a custom SQL report. That said - I'm no expert on datetime stuff.

Can one of our Report Gurus or a community member with experience in custome datetime jump in?

Andy

mhh351

I still have not heard anything from anyone, Andy. When I tried to e-mail you a reply, the server at your end failed for several days.

Could you give me some status in an e-mail, please?

Thanks!

mcbridea

Hello,

Were looking for a great post from last year that explained a bunch of options for modifying datatime. We don't write custom queries but I'll try to find that post and it will probably get you going in the right direction.

FormerMember

Mark,

Can you provide a sample report or query that you are using? I have done something similar for nodes but need to see what you are looking for to see if I can adapt it.

mhh351

Guys, Thanks for trying to assist.

Here is a shortened copy of a report write SQL where I am getting the Last Month statistics.

That is a good thing. Additionally, I would like to get any given set of days, such as month end days and any one week. I would write the reports to reflect EACH week.

Further, if I could get this on graphs, it would be even better! Reports will do for now.

Thanks for the guru help!

SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Interfaces.InterfaceName AS Interface_Name,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps

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 40481 AND 40510.9999884259 )
AND
(
(
   (Nodes.Caption LIKE '%namehere%') AND
   (
    (Interfaces.InterfaceName = 'FastEthernet0/1'))) OR
(
   (Nodes.Caption LIKE '%anothername%') AND
   (
    (Interfaces.InterfaceName = 'Serial0/0/0:0.777')))
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.InterfaceName

ORDER BY SummaryMonth ASC, 4 ASC, 5 ASC

njoylif

I've never been able to make the "40481" type numbers work well when going back more than a week and definitely not when needing "1st of month" type things.

This can be very complex so my suggestion is you start with a chunk of data (using where clause) from a specific node or interface where you are not worried if something happens to it. Limit your result set.

then start playing with the datetime piece of it (just add to where clause and work it until you get the desired results.

Here are a few posts to help you get going (search for dateadd - you could also search for datepart):

The online docs are really good too (microsoft - you'll want to pick the version you are running):

http://msdn.microsoft.com/en-us/library/bb418440%28v=SQL.10%29.aspx

if you review these links, work on some data samples and still have questions, shoot me an email or give me a call.