REFERAL LINK: Alert History - Cleared Time - Forum - Network Performance Monitor (NPM) - THWACK (solarwinds.com)
Have referred above URL and converted into sql query with some modifications as per our requirement such as
- Clear time col. was not displaying the msg 'Still active' hence converted that using case [ highlighted in Green ]
- [Minutes/Hours Until Clear] Col. has been modified to show the time in minutes/hours when the time difference is above 60 minutes [ highlighted in Purple ]
- Selective Alert KPI profile name [ highlighted in Orange ]
- For last week report only. Time is based upon the format like for Ex: 23/12/22 12 AM [ highlighted in Yellow ]
select ac.Name,ah.Message ,
case When ao.RelatedNodeCaption!= ao.EntityCaption THEN concat(isnull(ao.RelatedNodeCaption,''),' - ',isnull(EntityCaption,''))
else EntityCaption End AS [ALERT OBJECT],
--end as [Minutes Until Acknowledged] ,
--ack.Message as [Acknowledged_Note] ,
ah.TimeStamp as [Trigger Time] ,
--,case when ack.timestamp is null then 'N/A' ,
--else tostring(minutediff(ah.TimeStamp,ack.timestamp)) ,
case
when reset.TimeStamp is NULL then 'Still Active' else concat(reset.TimeStamp,+'')
END as [Clear Time] ,
case
when reset.timestamp is NULL then 'Still Active'--DATEDIFF(MINUTE,ah.TimeStamp,GETDATE())
when DATEDIFF(MINUTE,ah.TimeStamp,reset.timestamp)>=60 then concat(DATEDIFF(MINUTE,ah.TimeStamp,reset.timestamp)/60,' hours')
else concat(DATEDIFF(MINUTE,ah.TimeStamp,reset.timestamp),' Minutes')
end as [Minutes/Hours Until Clear]
FROM [dbo].[AlertHistory] ah
left join [dbo].[AlertObjects] ao on ao.alertobjectid=ah.alertobjectid
left join [dbo].[AlertConfigurations] ac on ac.alertid=ao.alertid
--left join [dbo].[Actions] a on a.actionid=ah.actionid
--left join (select timestamp, AlertActiveID, AlertObjectID,message from [dbo].[alerthistory] ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
left join (select timestamp, AlertActiveID, AlertObjectID from [dbo].[alerthistory] ah where eventtype in (1,8))
reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID
WHERE
ah.eventtype=0
--and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')
--and tostring(isnull(tolocal(reset.TimeStamp),'Still Active'))<>'Still Active'
and ac.name in('Node Down SF Alert'
,'High packet loss Alert'
,'High Memory alert'
,'High CPU alert - Critical'
,'F5 Volume alert'
,'High Interface Utilization'
,'Interface Down'
,'Interface Down - Switch and FEX Uplink')
and ah.timestamp between
concat(DATEADD(dd, -8, DATEDIFF(dd, 0, GETDATE())),'')
and
concat(DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),'')
order by ah.timestamp desc
