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.

SWQL query to return all acknowledged alerts

ouberlord over 10 years ago

Recently we upgraded to NPM 10.6, and for the longest time we've wanted to have some sort of report to show only acknowledged alerts. The idea was that the report could be ran daily to show the currently ack'ed alerts that have been so for longer than a few days, and a supervisor could check in with the team member to see what the progress is, or why the alert has been acknowledged for so long without being cleared. This has been something of an issue for us, as acknowledged alerts lose a lot of their visibility even to those who acknowledged it and then let it slip their minds.

This is the SWQL I came up with:

SELECT Nodes.Caption AS [Name], AlertDefinitions.Name AS [Alert Name],
tolocal(AlertStatus.TriggerTimeStamp) AS [Alert Time], tolocal(AlertStatus.AcknowledgedTime) AS [Acknowledged Time],
AlertStatus.AcknowledgedBy AS [Acknowledged By], AlertStatus.Notes
FROM Orion.AlertStatus
INNER JOIN Orion.Nodes
ON AlertStatus.ActiveObject = Nodes.NodeID
INNER JOIN Orion.AlertDefinitions
ON AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
WHERE Acknowledged = '1' AND DayDiff(AlertStatus.AcknowledgedTime, getdate())>2

This results in the following output:

Name

Alert Name

Alert Time

Acknowledged Time

Acknowledged By

Notes

ComputerName

High RAM Utilization

9/27/2013 10:27:48 AM

9/27/2013 11:02:08 AM

DOMAIN\Username- Orion Website

Acknowledged:Acked for testing of Orion's reporting functionality.

Credit to Jan Pelousek for help in figuring out how to convert the times from UDT into Local time, and for showing me the light in how to set up the date differential logic. Though the current Orion SDK (v1.7) does not support the ToLocal() function, NPM 10.6 does.

Top Replies

Diner over 10 years ago +2

I'd like also add to the last comment, that in NPM 10.6 vs. SDK 1.7 there's possible to use the function toLocal() to convert the time from UTC to the client's local time and functions like DayDiff (or…

0 Diner over 10 years ago

I'd like also add to the last comment, that in NPM 10.6 vs. SDK 1.7 there's possible to use the function toLocal() to convert the time from UTC to the client's local time and functions like DayDiff (or YearDiff, MonthDiff, WeekDiff, DayDiff, HourDiff, MinuteDiff, SecondDiff) to expand the WHERE condition to filter it by time the desired way.
For example (and to show the syntax) the query showing the alerts acknowledged before less than two days will look like:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT Nodes.Caption AS [Name], AlertDefinitions.Name AS [Alert Name],
tolocal(AlertStatus.TriggerTimeStamp) AS [Alert Time], tolocal(AlertStatus.AcknowledgedTime) AS [Acknowledged Time],
AlertStatus.AcknowledgedBy AS [Acknowledged By], AlertStatus.Notes
FROM Orion.AlertStatus
INNER JOIN Orion.Nodes
ON AlertStatus.ActiveObject = Nodes.NodeID
INNER JOIN Orion.AlertDefinitions
ON AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
WHERE Acknowledged = '1' AND DayDiff(AlertStatus.AcknowledgedTime, getdate())<2
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Cancel
Vote Up +2 Vote Down

Cancel
0 ouberlord over 10 years ago

Oh, excellent! The toLocal() function doesn't seem to work in the current version of the SDK (as you mentioned) but it certainly does in NPM 10.6 for the purposes of an actual report. Do you know if there is a list of the current SWQL functions available within NPM? I am only aware of the list in the SDK's documentation, and even then I'm not sure if it is complete.
For clarification purposes, in our usage the less than symbol would be flipped, and I didn't do the best job describing our intent. Our report is set up to show ack'ed alerts older than X days, so for one content block we have "DayDiff(AlertStatus.AcknowledgedTime, getdate())>2" (older than two days) and for another we have "DayDiff(AlertStatus.AcknowledgedTime, getdate())>7" (older than seven days).
Cancel
Vote Up 0 Vote Down

Cancel

0 Diner over 10 years ago

The list, which Tim posted in http://thwack.solarwinds.com/message/198138#198138 should be still valid.The missing functions are ToLocal() and ToUtc() and following ones which are available in NPM 10.6.

DateTrunc

2013.1

SELECT DateTrunc('month', DateTime) FROM Orion.CPULoad

Supported values for the DatePart argument: Minute, Hour, Week, Day, DayOfYear, Month, Quarter, Year.
Not supported: Millisecond, Second.

ChangeTimeZone

2013.1

SELECT ChangeTimeZone(DateTime, '+05:00') FROM Orion.CPULoad

The second argument needs to follow the standard timezone offset +/-hh:mm .

The date in the database will have its offset changed to the specified timezone offset.

Regarding the issue you stated (SDK 1.7 doesn't support ToLocal() function), could you please be more specific? It's enough to describe how it behaves to my email. I'm asking because my SDK 1.7 doesn't have any problem with it (using SWQL studio). Just note you need to use the alias in the query when using the function(e.g. Select toLocal(LastBoot) as [Boot] from orion.nodes)

0 ouberlord over 10 years ago

Sorry for the delay, I was ill on Tuesday.
My assumption could be incorrect on if the Orion SDK supports ToLocal (and I'd imagine you know better than I). When I try that query in my SWQL Studio I get an error that states:
"The communication object, System.ServiceModel.Security.SecuritySessionClientSettings`1+ClientSecurityDuplexSessionChannel[System.ServiceModel.Channels.IDuplexSessionChannel], cannot be used for communication because it is in the Faulted state."
When I remove the two ToLocal aspects of it, it runs as one would expect.
Cancel
Vote Up 0 Vote Down

Cancel
0 l.mendoza over 8 years ago

Hi, Jan.
Is this applicable with NPM 11.5 as well?
Please advise. Thank you.
Kind Regards,
Leny
Cancel
Vote Up 0 Vote Down

Cancel