Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SWQL Custom Query with Department field

Hi everyone,

I'm currently working on creating a chart on my SolarWinds Dashboard, but it seems I need to better understand the Orion SDK.

In my table, I want to display the specific department for each node that has an active alert.

Any ideas? Thank you very much!

SELECT
o.AlertConfigurations.Name AS [ALERT NAME]
,o.EntityCaption AS [SERVER NODE]
,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
,o.NodesCustomProperties.Department AS [RESPONSIBLE TEAM]
,CASE
WHEN o.AlertConfigurations.Severity = 1 then 'Critical'
WHEN o.AlertConfigurations.Severity = 2 then 'Serious'
WHEN o.AlertConfigurations.Severity = 3 then 'Warning'
WHEN o.AlertConfigurations.Severity = 4 then 'Informational'
WHEN o.AlertConfigurations.Severity = 5 then 'Notice'
END AS [Severity]
, o.AlertActive.Acknowledged
, o.AlertActive.AcknowledgedBy
, o.AlertActive.AcknowledgedDateTime
, o.AlertActive.AcknowledgedNote
FROM Orion.AlertObjects o
WHERE o.AlertActive.TriggeredMessage <> ''
AND o.AlertConfigurations.Severity = 1
ORDER by o.AlertActive.TriggeredDateTime DESC

When I try to validate, it returns this error:

Find more posts tagged with

swql query

SWQL

Accepted answers

bourlis

Ok, good news at least I hope it's good news . I was able to reproduce your issue and find your 'trouble child'.

First off, here's your issue:

WHERE o.NodesCustomProperties.Department <> ' ' AND o.AlertConfigurations.Severity = 1

You fixed the node.custom properties in your SELECT field but missed it your WHERE command.

Simply fix the WHERE command to "o.node.CustomProperties" and all will be right with this query.

Also you can go with the <> ' ' option or you can go with IS NOT NULL option. See below for both options.

WHERE o.Node.CustomProperties.Department IS NOT NULL and o.AlertConfigurations.Severity = 1

WHERE o.Node.CustomProperties.Department <> ' ' and o.AlertConfigurations.Severity = 1

bourlis

Hi alanaraki ,

Yes I do and it's my pleasure. To be clear you only want to see critical & warnings, correct? If so, you have 2 options which are::

WHERE o.AlertActive.TriggeredMessage <> ''
AND (o.AlertConfigurations.Severity = 1 or o.AlertConfigurations.Severity = 2)

WHERE o.AlertActive.TriggeredMessage <> ''
AND o.AlertConfigurations.Severity in (1,2)

When I first started learning SWQL I went with the 'or' option then when I learned about the 'in' option it was a game changer and now that's my go to.

Now, just for fun here's a couple of nuggets for you. If you wanted to see critical, serious and warning you can do these

where X in (1,2,3)

where X between 1 and 3

where X <= 3

All comments

flackovic

Instead of:

,o.NodesCustomProperties.Department AS [RESPONSIBLE TEAM]

What happens if you use:

,o.Node.CustomProperties.Department AS [RESPONSIBLE TEAM]

This should use the proper navigation property.

alanaraki

Hi there @flackovic

It still bring an error, but seems to be different now.

It seems that it is requiring me to add another entity to be able to pull the data from CustomProperties itself.

bourlis

HI alanaraki,

I see 2 items in your query that's giving you grief, all you have to do it simply change:

,o.NodesCustomProperties.Department AS [RESPONSIBLE TEAM]

,o.Node.CustomProperties.Department AS [RESPONSIBLE TEAM]

and your query will work like butter!

Here's 2 items that'll help you next time.

#1 double check your verbiage in Orion.SKD with what it shows you as joinable or link-able tables.

#2 is to use let Orion.SKD populate thigs for you. You're only option for a link-able table is 'Node', so you need a (.) after node, then you type your next link-able table, which is customproperties.

alanaraki

Hi there @bourlis

Firstly, thanks for trying to help.

It was the same as @flackovic recommended, but it is still returning an error

Cheers