cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Node Uptime Report (swql)

Someone asked for a node up report and I came up with this one.  The person asked for the ability to search for vendor as well as node name and IP address.  And duration in a easy to read format.

SELECT

n.Caption AS [Node Name]

,DetailsUrl AS [_LinkFor_Node Name]

,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name]

,n.IP_Address as IP_Address

,DetailsUrl AS [_LinkFor_IP_Address]

,'/NetPerfMon/Images/Vendors/' + n.VendorIcon as [_IconFor_IP_Address]

,tostring(tolocal(n.LastBoot)) as LastBoot

,CONCAT(HOURDIFF(tolocal(n.LastBoot),getdate())/24,' Day(s) ',

        HOURDIFF(tolocal(n.LastBoot),getdate())-(HOURDIFF(tolocal(n.LastBoot),getdate())/24)*24,'h ',

        MINUTEDIFF(tolocal(n.LastBoot),getdate())-(MINUTEDIFF(tolocal(n.LastBoot),getdate())/60)*60,'m') AS Duration

FROM Orion.Nodes n

WHERE n.LastBoot IS NOT NULL

and

(n.caption like '%${SEARCH_STRING}%'

or n.ip_address like '%${SEARCH_STRING}%'

or n.vendor like '%${SEARCH_STRING}%')

ORDER BY MINUTEDIFF(tolocal(n.LastBoot),getdate())  desc

pastedImage_0.png

Thanks

Amit

Labels (2)
Tags (3)
Comments

What resource did you use to utilize the search?

This is the Custom Query resource with the "search" box checked

How do we force the last boot time to be updated?

I have just installed the Agent on several systems but I also have SNMP on others.

The last boot doesn't seem to be updating timely. I had a box that was rebooted 12 days ago, but SolarWinds was reporting that it was last booted back in 2014.

How can I force this to be updated. Re-discover doesn't seem to be doing it.

I'm trying to create a report that includes certain "vendors" and excludes others (i.e.include "Windows" but NOT "Cisco").  And be able to export it to Excel.  Any ideas or better SQL syntax?

add to the where section:

and n.vendor not in ('vendor1','vendor2','etc')

or if the list is shorter you could go the other way and say vendor in such and such

my requirement is  timestamp in below query. when nodes was down

SELECT City, Location ,StatusLED,SP1,SP2,Caption  FROM [dbo].[Nodes]

WHERE Status = '2' and ( SP1 in ( 'x', 'x', 'x', x', 'x','x')

OR  SP2 in ( 'x', 'x', 'x', x', 'x','x'))

and ( CITY in ('x', 'x', 'x', 'x', 'x', 'x')

OR Location in (x', 'x', 'x', x', 'x','x') )

getting headers and that's all when I try to use this.  Thoughts?

Comment out the and or lines with the search string stuff

got it working.  thanks!

Hi, would you please share the last edited query. I am trying but somehow not able to make it work. how exactly the end product look like ?

SELECT

n.Caption AS [Node Name]

,DetailsUrl AS [_LinkFor_Node Name]

,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name]

,n.IP_Address as IP_Address

,DetailsUrl AS [_LinkFor_IP_Address]

,'/NetPerfMon/Images/Vendors/' + n.VendorIcon as [_IconFor_IP_Address]

,tostring(tolocal(n.LastBoot)) as LastBoot

,CONCAT(HOURDIFF(tolocal(n.LastBoot),getdate())/24,' Day(s) ',

        HOURDIFF(tolocal(n.LastBoot),getdate())-(HOURDIFF(tolocal(n.LastBoot),getdate())/24)*24,'h ',

        MINUTEDIFF(tolocal(n.LastBoot),getdate())-(MINUTEDIFF(tolocal(n.LastBoot),getdate())/60)*60,'m') AS Duration

FROM Orion.Nodes n

WHERE n.LastBoot IS NOT NULL

and

n.vendor not in ('vendor1','vendor2','etc')

(n.caption like '%${SEARCH_STRING}%'

or n.ip_address like '%${SEARCH_STRING}%'

or n.vendor like '%${SEARCH_STRING}%')

ORDER BY MINUTEDIFF(tolocal(n.LastBoot),getdate())  desc

thanks!

Hi Amit @cscoengineer , and others contributing to this thread.

To state what will be obvious due to my question, I am new, and a student. I have unsuccessfully been trying to locate a built in Node uptime report within NPM, or the fields needed in the built in Manage Reports/Create New Report process. I found the original post outlining the perfect report.

 

Can I get a little guidance about what to do with the report string which was written? Do I format it into a file which can be imported?

 

Thanks for any help,

Denis

Version history
Revision #:
1 of 1
Last update:
‎01-16-2018 02:22 PM
Updated by: