3 Replies Latest reply on Apr 27, 2016 2:50 PM by tdanner

    Converting UTC times in SWQL results




      I am trying to write an SWQL query that will sum a statistic for a SAM counter by day for the last 7 days.  The query works, but the times returned are UTC so don't translate correctly into local days.  Is there a function in SWQL to convert timezones or add hours to a timestamp?  DATEADD doesn't seem to be available.  On the same subject, is there a list of available SWQL functions anywhere?  I couldn't see on in the SDK docs or find one online.








          decs.Label AS Stat,

          month(cs.TimeStamp)AS Month,

          day(cs.TimeStamp) AS Day,

          SUM(de.AvgNumericData) AS TxPerDay

      FROM orion.apm.Component c

      INNER JOIN orion.apm.ComponentStatus cs

          ON c.ComponentID = cs.ComponentID

      INNER JOIN orion.apm.DynamicEvidenceColumnSchema decs

          ON c.ComponentID = decs.ComponentID AND decs.Type = 1

      INNER join orion.apm.DynamicEvidence de

          ON cs.ID = de.ComponentStatusID AND decs.ColumnSchemaID = de.ColumnSchemaID

      WHERE decs.Label = 'PV TPM' AND DAYDIFF(cs.TimeStamp, getdate()) < 7

      GROUP BY




      ORDER BY

          month(cs.TimeStamp) DESC,

          day(cs.TimeStamp) DESC

        • Re: Converting UTC times in SWQL results

          You may find that posting this question in the Orion SDK forum will yield more responses than posting it here in the SAM forum.

          1 of 1 people found this helpful
          • Re: Converting UTC times in SWQL results

            I know this is an old post but i was looking for the same info and found this post first, so just in case any one else ends up in the same boat I'm in.  I was trying to pull the last time a server rebooted as well as the last time windows updates were installed. But i was running into the same UTC issue. 


            It does not appear that you can use the DateAdd function in SWQL.


            In my case i was trying to convert from UTC to EST so i was able to use the AddHour function instead. (haven't tested if there are these functions for each time increment, I assume they are all there)



            Here is my final code with the addition, see the bold text.



            n.SysName as Host_Name,

            AddHour(-4,n.LastBoot) as Last_Reboot ,

            MAX(o.installdate) as Last_Update

            from Orion.nodes as n

            Left outer join

            Orion.AssetInventory.OSUpdates as o

            on n.nodeid = o.nodeid

            where n.nodename like 'SERVER_NAME%'

            Group By n.sysname, n.nodeid, n.lastboot

            order by n.sysname ASC