Report ~ Specific Date/Node Range

vcampos

Hello everyone,

Trying to create a report where it shows up times for an indicated date range. Also, not

sure if there is a statement where I can isolate to specified nodes. Below is what I got/found so far

but I'm sure the statements are incorrect. Thank you.

SELECT

NodeID

,Caption

,VendorIcon

,Ip_Address

,DateTime

,DateDiff(hour,Nodes.LastBoot,GetDate()) AS HoursUp

,CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())/(24*60))

+ ' days, '

+ CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%(24*60)/60)

+ ' hours, and '

+ CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%60)

+ ' minutes.' AS 'Time Up'

WHERE DateTime Between '2015-08-19' AND '2016-06-20'

FROM [Nodeid: 2821,2524,2625]

ORDER BY 5 DESC

Find more posts tagged with

report writer

web reports

Accepted answers

rickrocks

Yes, I checked the Nodes table and it doesn't have a DateTime column. The DateTime column is applicable only to those tables that have node statistics like ResponseTime, VolumeUsage, InterfaceTraffic, etc. I recommend you use the ReportWriter tool from your server to generate reports that return these kind of data from your network elements.

All comments

rickrocks

Hello, try changing your query as follows:

SELECT

NodeID

,Caption

,VendorIcon

,Ip_Address

,DateTime

,DateDiff(hour,Nodes.LastBoot,GetDate()) AS HoursUp

,CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())/(24*60))

+ ' days, '

+ CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%(24*60)/60)

+ ' hours, and '

+ CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%60)

+ ' minutes.' AS 'Time Up'

FROM [dbo.][Nodes]

WHERE (DateTime Between '2015-08-19' AND '2016-06-20')

AND NodeID in (2821,2524,2625)

ORDER BY 5 DESC

Your SELECT statement will return records FROM 'Nodes' table following a two conditions which is your datetime and specific nodes. Hope this helps

vcampos

Sorry for the late reply, been off for a few days. Thanks for the reply rickrock but the statements didn't work. I think the DateTime column doesn't exist in my database. I even tried just using the FROM [dbo.][Nodes] statements but my reporting doesn't like it. It only likes the Nodes table. How would I know if my database has the DateTime column?

rickrocks

vcampos

Thank you rickrocks! I was able to generate something close using the ReportWriter.