Display alerts for all children in a group

madscomer

I have a view with a limitation on a group containing nodes. The nodes contain SAM applications.

How does one present alerts for all children in a group without adding the SAM applications to the group as well?

I am using this query to present cleared alerts for objects in the view limitation, but the AlertObjects table hides SAM apps and components that don't have their SAM app objects directly in the group.

SELECT TOP 1000
tolocal(reset.TimeStamp) as [Clear Time]
,ac.Name as [Alert Name]
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Alert Name] 
,CASE  
    WHEN ao.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'  
    WHEN ao.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'  
    WHEN ao.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'  
    WHEN ao.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
    WHEN ao.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'  
END AS [_iconfor_Alert Name]
,CASE
    When RelatedNodeCaption!= EntityCaption THEN concat(isnull(RelatedNodeCaption,''),'\',isnull(EntityCaption,''))
    ELSE EntityCaption
END AS [Triggered By]
,EntityDetailsURL AS [_LinkFor_Triggered By]
,minutediff(ah.TimeStamp,reset.timestamp) as [Minutes To Clear]
,tolocal(ah.TimeStamp) as [Trigger Time]
FROM Orion.AlertHistory ah
INNER join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid 
left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid 
left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype in (1,8)) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID 
WHERE ah.eventtype=0
and reset.TimeStamp IS NOT NULL
--and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%' or ah.Message like '%${SEARCH_STRING}%') 
 order by reset.TimeStamp desc

Events don't seem to have this problem. This is showing me SAM apps and component events contained in the nodes within the group.

SELECT TOP 2000
ToLocal(Event.EventTime) AS [Event Time]
,EventTypes.Name as [Event Type]
,CONCAT('/NetPerfMon/images/Event-',EventTypes.EventType,'.gif') as [_IconFor_Event Type]
,Event.Message as Message
,CASE
WHEN Event.NetObjectId IS NULL 
    or Event.NetObjectType IS NULL
    or Event.NetObjectType = ''
    or Event.NetObjectType like 'V:%'
  THEN ''
WHEN Event.NetObjectType ='AA' THEN CONCAT('/Orion/View.aspx?NetObject=AA:',Event.NetObjectID)
WHEN Event.NetObjectType ='ABSA' THEN CONCAT('/Orion/View.aspx?NetObject=ABSA:',Event.NetObjectID)
WHEN Event.NetObjectType ='AM' THEN CONCAT('/Orion/View.aspx?NetObject=AM:',Event.NetObjectID)
WHEN Event.NetObjectType ='HWH' THEN CONCAT('/Orion/View.aspx?NetObject=HWH:',Event.NetObjectID)
WHEN Event.NetObjectType ='HWHS' THEN CONCAT('/Orion/View.aspx?NetObject=HWHS:',Event.NetObjectID)
WHEN Event.NetObjectType ='HWHT' THEN CONCAT('/Orion/View.aspx?NetObject=HWHT:',Event.NetObjectID)
ELSE CONCAT('/Orion/View.aspx?NetObject=',Event.NetObjectType,':',Event.NetObjectID)--oddly this may add trailing spaces to the NetObjectType which breaks links, use case for such situations
END AS [_LinkFor_Message]
FROM Orion.Events AS Event
LEFT JOIN Orion.EventTypes AS EventTypes ON EventTypes.EventType = Event.EventType
WHERE Event.EventTime >= ADDDAY(-14, GETUTCDATE())
--and EventTypes.Name like '%${SEARCH_STRING}%' or Event.Message like '%${SEARCH_STRING}%'
ORDER BY Event.EventTime DESC

Thanks for the help, this community is fantastic!