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

    Converting UTC times in SWQL results

    patgade

      Hi,

       

      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.

       

      Thanks,

       

      Patrick

       

       

      SELECT

          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

          decs.Label,

          month(cs.TimeStamp),

          day(cs.TimeStamp)

      ORDER BY

          month(cs.TimeStamp) DESC,

          day(cs.TimeStamp) DESC

        • Re: Converting UTC times in SWQL results
          aLTeReGo

          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
            tszilagyi

            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.

             

            Select

            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