SWQL Custom Query with Department field

Instead of:

,o.NodesCustomProperties.Department AS [RESPONSIBLE TEAM]

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

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.

HI alanaraki,

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

,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.

Hi there bourlis 

Firstly, thanks for trying to help.

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

Cheers

I'm sorry to ask such a basic question, but is the script you provided above the exact one that in your Dashboad query?  Can you please supply a screenshot of the SWQL query as it sites in the dashboard?

I only ask because when I take the script you provided, including the o.Node.CustomProperties and put into my SWQL studio and my Dashboard editor it works.  All I can think of is that maybe the script you have in the Dashboard is slightly different than the one originally posted. 

Hi there bourlis 

Don't need to say sorry, I actually appreciate your interest in help me. Thanks again.

So, basically this is the code that are currently applied in my SW Dashboard, but actually I don't want the alert description column (RESPONSIBLE TEAM COLUMN). In that column I want respective team that are settled in the Custom Field "Department"

And the code that I'm using is the one below:

SELECT
o.AlertConfigurations.Name AS [ALERT NAME]
,o.EntityCaption AS [SERVER NODE]
,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
,o.Node.CustomProperties.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.NodesCustomProperties.Department <> ''
AND o.AlertConfigurations.Severity = 1
ORDER by o.AlertActive.TriggeredDateTime DESC

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.

Thank you very much bourlis it indeed worked. 

Hi bourlis 

Please, allow me to do another question.

with your help, my table is now working. Thank you for that.

SELECT
o.Node.Caption AS [ALERT NAME]
,o.EntityCaption AS [ALERT OBJECT]
,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
,o.Node.CustomProperties.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
FROM Orion.AlertObjects o
WHERE o.AlertActive.TriggeredMessage <> ''
AND o.AlertConfigurations.Severity = 1 
ORDER by o.AlertActive.TriggeredDateTime DESC

I'd like to know how can I make sure it will display not just the Critical alerts ("1") but either shows the Warning Alerts ("3"). Would you know?

I notice that this is the line ("AND o.AlertConfigurations.Severity = 1 ") that would determine, but not sure how should I handle this to have the return that I expect.

Thanks!

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)

or

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