SWQL OS Uptime Report

rhurbi1

Looking to create a simple report based on Caption, IP Address, and OS Uptime.

I created a report myself using the advanced selector options, but fell short as the Uptime was displayed in seconds only. Looks like the OS Uptime Time filter was not compatible with the Data Unit option, so was unable to set the time format to XX Days XX hours XX Minutes.

I did find a SWQL option for Node.LastBoot, but that is not directly related to our VMs but is the last boot of the ESX host.

Node.LastBoot SWQL:

SELECT

NodeID
,Caption
,Ip_Address
,MachineType
,DateDiff(hour,Node.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 [Nodes]
where Nodes.MachineType Like '%Server%'
and Nodes.MachineType not Like '%VMware%'
and Nodes.MachineType not Like '%decServer%'

ORDER BY 5 DESC

If someone can assist on getting this SWQL to display on OS Uptime for our  VM's or new SWQL option for OS Uptime, it would be greatly appreciated!

Thanks!

rhurbi1

Wrote this up and am using for now, but open to more options:

SELECT
n.Caption
,n.IP_Address
,n.SystemUpTime
,ROUND(n.SystemUpTime/3600,2) AS [SystemUpTime_Hours]
,ROUND(n.SystemUpTime/86400,2) AS [SytemUpTime_Days]

FROM orion.Nodes n

Where n.vendor like '%Windows%'
or n.vendor like '%Windows%'

ORDER BY n.SystemUpTime DESC