0 Replies Latest reply on Apr 10, 2017 6:44 PM by mesverrum

    Unpredictable results with tostring() on floats in SWQL

    mesverrum

      So I have some queries for my dashboards where I wanted to know how long it has been since certain events happened.  For the longest time I was just using minutediff() and that was alright, but I recently decided I wanted to make it a little nicer and break things out into days/hours/minutes as necessary.  So far the best solution I cam up with is demonstrated in this query:

       

      SELECT
      o.AlertConfigurations.Name AS [ALERT NAME]
      ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
      ,o.AlertConfigurations.Severity
      ,CASE
      WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
      WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
      WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
      WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
      WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
      END AS [_iconfor_ALERT NAME]
      ,o.EntityCaption AS [ALERT OBJECT]
      ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
      ,case 
      WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'
      When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
      End as [RELATED NODE]
        ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
        ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
      -- ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]
      --,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_ALERT OBJECT]
      ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]
      ,CASE
      when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
      when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
      else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
      end as [Time Active]
      ,o.AlertActive.AcknowledgedBy
      
      FROM Orion.AlertObjects o
      join Orion.AlertActive aa on aa.alertobjectid=o.alertobjectid
      LEFT join Orion.Nodes p on p.nodeid=relatednodeid
      where   o.entitytype not like 'orion.VIM%'
      
      ORDER by o.AlertActive.TriggeredDateTime DESC
      

       

       

      This was pretty good so then I took this same logic and applied it to another one of my other queries but now instead of getting a nice clean round I'm getting a ton of trailing zeros

       

      SELECT
      n.caption as Node
      ,n.detailsurl as [_linkfor_Node]
      ,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
      ,n.ip_address as [IP Address]
      ,n.detailsurl as [_linkfor_IP Address]
      ,case 
      when n.LASTSYSTEMUPTIMEPOLLUTC is null then 'Never'
      else tostring(ToLocal(n.LASTSYSTEMUPTIMEPOLLUTC))
      end as [Last Polled]
      ,CASE
      when minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,GETUTCDATE())>1440 then (tostring(round(minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,GETUTCDATE())/1440.0,1)) + ' Days')
      when minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,GETUTCDATE())>60 then (tostring(round(minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,GETUTCDATE())/60.0,1)) + ' Hours')
      else (tostring(minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,GETUTCDATE())) + ' Minutes')
      end as [Time Down]
      
      from orion.nodes n
      Where status=2
      
      order by LASTSYSTEMUPTIMEPOLLUTC desc
      

       

       

      So I've been unable to figure out what is different about the two cases that is producing these extra digits, anyone have any ideas about what is going on or how I can display what I want in a different way?