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:
SELECTo.AlertConfigurations.Name AS [ALERT NAME],'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME],o.AlertConfigurations.Severity,CASEWHEN 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 RelatedNodeCaptionEnd 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],CASEwhen 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.AcknowledgedByFROM Orion.AlertObjects ojoin Orion.AlertActive aa on aa.alertobjectid=o.alertobjectidLEFT join Orion.Nodes p on p.nodeid=relatednodeidwhere 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
SELECTn.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],CASEwhen 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 nWhere status=2order 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?
