This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL: SystemUptime Seconds to Days Conversion.

Hi,

I'm hoping that someone would be kind enough to help my modify this custom query please.

It displays all nodes within our environment with an uptime of greater than 30days, which is great except for the fact that Systemuptime is in seconds which makes it difficult to work with.

So

1) Could someone please help me add in a seconds to days conversion please? I know it would be 2592000/86400 but just not sure how to implement it into a query.

2) This is capturing  All Nodes. Ideally we just want it to capture Servers which are recorded within our environment with a customproperty of Device_Type Server.

Any help would be greatly appreciated.

SELECT n.Caption, n.IP_Address, n.SystemUptime FROM orion.Nodes n

where n.systemuptime > 2592000

order by n.systemuptime desc

pastedImage_3.png

  • Here's a few options for you:

    SELECT
    n.Caption
    ,n.IP_Address
    ,n.SystemUptime AS [SystemUpTime_Seconds]
    ,ROUND( n.SystemUpTime / 86400, 2 ) AS [SystemUpTime_Days_Rounded]
    ,FLOOR( n.SystemUpTime / 86400 ) AS [SystemUpTime_Days_Rounded_Down]
    ,CEILING( n.SystemUpTime / 86400 ) AS [SystemUpTime_Days_Rounded_Up]
    FROM Orion.Nodes n
    WHERE n.SystemUpTime > 2592000
    ORDER BY n.SystemUpTime DESC
  • If I may jump in here.... I am interested in this as well, but was hoping to be able to apply the proper conversion/formatting directly to the column variable in the report without resorting to custom SQL. Is custom SQL the only way to do this at this time?

  • The more the merrier!  Before i put in my first SWQL request in,  I to had tried to find a none SQL approach to but couldn't, it was certainly my preference to avoid it also.

    In the end I actually changed my original approach and instead opted to show nodes by their last reboot time instead.

    It meant I could query them for any node which had a reboot outside of the last month and this seems to do the job more neatly than my first query (and without the need for conversion)

    SELECT n.Caption,n.LastBoot FROM orion.Nodes n

    Where n.vendor like '%windows%'

    and MonthDiff(n.Lastboot, getdate())>1

    order by n.LastBoot asc

  • I am not on VPN right now to confirm, but you might want to make sure that the n.lastboot value is not in UTC since you are using SWQL and it converts nearly everything to UTC.  if it is, then just change your getdate() to getutcdate() to be accurate.  A few hours probably doesn't matter in a 30 day reboot report but other queries you work with might be more sensitive to the time zone offsets.