cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Modification in Report Required ! Please Help

Hi All !

I am using mute alert report that is written in SWQL. (Pasting the code below).

My requirement is that we should have a comment field (lets say i can request users to write that in Notes field why did they put it in mute alerts). And then the notes field is also displayed as additional column.

Currently the code is below (please help to ammend this) so that notes field is added at the end.

SELECT EntityUri, UtcSuppressFrom, UtcSuppressUntil, LocalSuppressFrom, LocalSuppressUntil, Element, DetailsUrl, AE2.AccountID

FROM

(

SELECT DISTINCT [EntityUri],

[SuppressFrom] AS [UtcSuppressFrom],

[SuppressUntil] AS [UtcSuppressUntil],

ToLocal([SuppressFrom]) AS [LocalSuppressFrom],

ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[Caption]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[FullName]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[FullyQualifiedName]

ELSE 'SomethingElse'

END AS [Element],

CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[DetailsUrl]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[DetailsUrl]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[DetailsUrl]

ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'

END AS [DetailsUrl]

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[NodeName]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[InterfaceCaption]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[Name]

ELSE 'Wrong'

END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

) AS tbl1

JOIN

(

SELECT NetObjectID, MAX(TimeLoggedUtc) AS MaxTimeLoggedUtc

FROM Orion.AuditingEvents AS AE

INNER JOIN Orion.AuditingActionTypes AS [AT]

ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

GROUP BY NetObjectID

) AS tbl2 ON tbl1.EntityUri LIKE CONCAT('%=', tbl2.NetObjectID)

JOIN Orion.AuditingEvents AS AE2 ON tbl2.NetObjectID = AE2.NetObjectID AND tbl2.MaxTimeLoggedUtc = AE2.TimeLoggedUtc

Tags (1)
0 Kudos
0 Replies