how can i add down date and time and duration

sadiqsul

Find more posts tagged with

Accepted answers

All comments

stuartd

How are you producing this?

Can you show us your S(W)QL code or a screenshot of your reports logic?

sadiqsul

SELECT [data].[DisplayName] AS [DisplayName],[data].[Application].[Node].[DisplayName] AS [DisplayName1],[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.apm.component AS data
WHERE
((([data].[Application].[Node].[CustomProperties].[TETCO_HQ_URL]) = ('TETCO-HQ-URL')))
WITH NOLOCK

stuartd

OK - so I don't have or use APM so can't explicitly answer.

For me, what I would do is open SWQL Studio, then navigate to the orion.apm.component table and search through the various fields there to identify if your required data is available (it might be available via the implicit joins). Once you've identified the fields, you will need to add those fields into the SELECT statement.

So, for e,g, let's say the field is called LastResponse for the down date then your SELECT statement could look like:

>> SELECT [data].[DisplayName] AS [DisplayName],[data].[Application].[Node].[DisplayName] AS [DisplayName1],[data].[InstanceSiteId] AS [InstanceSiteId], LastResponse AS [Down Date]

sguido

I don't know the field in the database you need to find but I can give you the math needed to turn the date into a current duration, just replace AlertActive.TriggeredDateTime with your field.

CONCAT(CASE
    WHEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)<720 THEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))/1440)
    ELSE ((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))/1440)-1)
END,'D ',
CASE
    WHEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440%60)<30 THEN ((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)/60)
    ELSE (((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)/60)-1)
END,'H ',
(MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440%60),'M'
) AS Duration

I use this on our alert screen, here is what the output looks like:

edwinhoekman

Hi Sadiq,

For me it is not really clear what you are looking for?

Do you want to see all dates and times when a specific component was down,
Only show the last time the down occured,
show only the current down components and the time they are down.

sadiqsul

hi edwin

i want see all dates and times as well down duration on the same report

sadiqsul

hi bro.

query is not valid

sguido

Interesting, so I didn't include any of the FROM or JOIN statements that you may need because I figured you'd be using a different field. I've included the complete query below. I use this for a table of a modern dashboard.

SELECT
(MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))) AS Seconds, AlertActive.TriggeredDateTime, ToLocal(AlertActive.TriggeredDateTime) AS LocalTriggerTime,AlertActive.TriggeredMessage,Nodes.Caption,Nodes.IP, Nodes.DetailsURL,AlertConfigurations.Severity AS Severity,
CONCAT('solarwinds.clemson.edu/.../ActiveAlertDetails.aspx AS AlertURL,
CONCAT(CASE
    WHEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)<720 THEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))/1440)
    ELSE ((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))/1440)-1)
END,'D ',
CASE
    WHEN (MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440%60)<30 THEN ((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)/60)
    ELSE (((MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440)/60)-1)
END,'H ',
(MinuteDiff(AlertActive.TriggeredDateTime,ToUtc(GetDate()))%1440%60),'M'
) AS Duration

FROM Orion.AlertActive AS AlertActive
INNER JOIN Orion.AlertObjects AS AlertObjects ON AlertActive.InstanceSiteId=AlertObjects.InstanceSiteId AND AlertActive.AlertObjectID=AlertObjects.AlertObjectID
LEFT JOIN Orion.Nodes AS Nodes ON AlertObjects.RelatedNodeId = Nodes.NodeID
INNER JOIN Orion.AlertConfigurations AS AlertConfigurations ON AlertObjects.AlertID=AlertConfigurations.AlertID
WHERE AlertActive.Acknowledged IS NULL
ORDER BY Seconds ASC

sadiqsul

hi all

i got exact query which i was looking for

SELECT

-- Device name

StartTime.Nodes.Caption AS [Device]

, starttime.nodes.detailsurl as [_linkfor_Device]

,'/Orion/images/StatusIcons/Small-' + starttime.nodes.statusicon AS [_IconFor_Device]

-- Application name

,a.Name

,a.detailsurl as [_linkfor_Name]

, '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' as [_iconfor_Name]

-- Down Event Message

,starttime.message as [Down Event Message]

-- Down Event time stamp in local time zone

,ToLocal(StartTime.EventTime) AS [Down Event]

-- Up Event Message

,(SELECT TOP 1

Message AS [Message]

FROM Orion.Events AS [EndTime]

WHERE EndTime.EventTime >= StartTime.EventTime

AND (EndTime.EventType = 504 -- up

OR EndTime.EventType = 510 -- warning

OR EndTime.EventType = 509 -- critical

OR EndTime.EventType = 512) -- resumed

AND EndTime.NetworkNode = StartTime.NetworkNode

AND EndTime.NetObjectType like 'a%'

AND EndTime.NetObjectID = StartTime.NetObjectID

AND EventTime IS NOT NULL

ORDER BY EndTime.EventTime

) AS [Up Event Message]

-- Up Event time stamp in local time zone

,(SELECT TOP 1

ToLocal(EventTime) AS [EventTime]

FROM Orion.Events AS [EndTime]

WHERE EndTime.EventTime >= StartTime.EventTime

AND (EndTime.EventType = 504 -- up

OR EndTime.EventType = 510 -- warning

OR EndTime.EventType = 509 -- critical

OR EndTime.EventType = 512) -- resumed

AND EndTime.NetworkNode = StartTime.NetworkNode

AND EndTime.NetObjectType like 'a%'

AND EndTime.NetObjectID = StartTime.NetObjectID

AND EventTime IS NOT NULL

ORDER BY EndTime.EventTime

) AS [Up Event]

-- Downtime duration in minutes. Based on either the current time (node is still down) or Up Event time (node is back up)

,CASE

WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]

FROM Orion.Events AS [EndTime]

WHERE EndTime.EventTime >= StartTime.EventTime

AND (EndTime.EventType = 504 -- up

OR EndTime.EventType = 510 -- warning

OR EndTime.EventType = 509 -- critical

OR EndTime.EventType = 512) -- resumed

AND EndTime.NetworkNode = StartTime.NetworkNode

AND EndTime.NetObjectType like 'a%'

AND EndTime.NetObjectID = StartTime.NetObjectID

AND EventTime IS NOT NULL

ORDER BY EndTime.EventTime) IS NULL THEN MINUTEDIFF(StartTime.EventTime, GETUTCDATE())

ELSE MINUTEDIFF(StartTime.EventTime,

(SELECT TOP 1 EventTime

FROM Orion.Events AS [EndTime]

WHERE EndTime.EventTime > StartTime.EventTime

AND (EndTime.EventType = 504 -- up

OR EndTime.EventType = 510 -- warning

OR EndTime.EventType = 509 -- critical

OR EndTime.EventType = 512) -- resumed

AND EndTime.NetworkNode = StartTime.NetworkNode

AND EndTime.NetObjectType like 'a%'

AND EndTime.NetObjectID = StartTime.NetObjectID

ORDER BY EndTime.EventTime))

END AS [Minutes]

-- This is the table we are querying

FROM Orion.Events StartTime

join orion.apm.application a on a.applicationid=starttime.netobjectid

-- EventType = 1 is to correlate with our 'Down Event' time stamp from line 13 above

WHERE (StartTime.EventType = 505 -- down and unknown

or StartTime.EventType = 513 -- unreachable

or StartTime.EventType = 511) -- suspended

and StartTime.NetObjectType like 'a%'

and daydiff(starttime.eventtime,getdate())<31

--remove the comment from this part to make this searchable in a custom query resource

-- and ( StartTime.Nodes.Caption like '%${SEARCH_STRING}%' or a.Name like '%${SEARCH_STRING}%')

-- Order our results from the newest to oldest 'Down Event'

ORDER BY [Down Event] DESC