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

    Unpredictable results with tostring() on floats in SWQL


      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:


      o.AlertConfigurations.Name AS [ALERT NAME]
      ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
      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]
      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]
      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]
      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


      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]
      when n.LASTSYSTEMUPTIMEPOLLUTC is null then 'Never'
      else tostring(ToLocal(n.LASTSYSTEMUPTIMEPOLLUTC))
      end as [Last Polled]
      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



      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?