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?
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?
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.
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.
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
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
( DateTime BETWEEN 40481 AND 40510.9999884259 )
(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
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):
if you review these links, work on some data samples and still have questions, shoot me an email or give me a call.