This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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="'">thwack.solarwinds.com/.../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