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

NULL return in SWQL query in alerts ?

Hello, I've got some SWQL which runs when an node reboots, counts the interfaces for some specific keywords and updates a customer field, all good except where there are no matching interfaces when the field just gets updated with the embedded SWQL code.

I've tried ISNULL, CASE, etc all the usual and they odd thing is they work in SWQL or SQL just when embedded into the alert if there no match it behaves differently.

Any idea's what I want is a 0 returned .

Mike

Find more posts tagged with

Accepted answers

dquerry

I think the problem is with your query. I got the same result as you when i had the "group by nodeid". I took that off the query and it ran and returned a 0. See the below query.

SELECT ISNULL(count(*),0) as Comments FROM Orion.NPM.Interfaces where (Interfaces.AdminStatus = 1) and (Interfaces.NodeId =888 ) and (Interfaces.InterfaceAlias like '%ACCS%' )

I'm not sure why you would need a group by since you are only selecting one node. Let me know if that doesn't help, but it resolved the issue on my system.

All comments

dquerry

Can you share your query?

fellowsm

Hello this is what is in the alert

On ${N=Generic;M=DateTime;F=DateTime} ACCS Interface count=$ ) and (Interfaces.InterfaceAlias like '%ACCS%' ) group by Nodeid}

Which works when there is matching interface(s) but when there are no entries this SQL get posted into the custom field like this , node 3624 has 4 matching /f’s node 2596 has none

2596

ACCS Interface count=$

3624

On 03 April 2017 15:19 ACCS Interface count=4

I’ve tried adding ISNULL, and doing a SELECT TOP after union with a ‘0’ value – all works okay in SWQL but not in the alarm

Mike

Mike Fellows | Senior OSS Specialist

Direct: 07701319753 | Mobile: 07701319753

E-mail: Mike.Fellows@telent.com | Web: www.telent.com<http://www.telent.com>

telent Technology Services Ltd. Registered in England. No 703317.

Registered Office: Point 3, Haywood Road, Warwick, CV34 5AH, England

telent Technology Services Limited is authorised and regulated by the Financial Conduct Authority for credit-related regulated activities

telent_new_logo1.png

dquerry

Im confused on where this code is coming from "On ${N=Generic;M=DateTime;F=DateTime} ACCS Interface count=$ ) and (Interfaces.InterfaceAlias like '%ACCS%' ) group by Nodeid}' can you include a screenshot?

fellowsm

Here you go, changing a custom property as part of an alarm, counting the interfaces that match the criteria and updating the customer variable to reflect the number

Mike Fellows | Senior OSS Specialist

Direct: 07701319753 | Mobile: 07701319753

E-mail: Mike.Fellows@telent.com | Web: www.telent.com<http://www.telent.com>

telent Technology Services Ltd. Registered in England. No 703317.

Registered Office: Point 3, Haywood Road, Warwick, CV34 5AH, England

telent Technology Services Limited is authorised and regulated by the Financial Conduct Authority for credit-related regulated activities

attachments.zip

dquerry

Would something like this work? Otherwise im out of ideas.

select isnull(count(interfaceid),0) as num from Orion.NPM.InterfacesCustomProperties where interfaceid=8888888888888888

fellowsm

Tried an isnull works in SWQL studio just not when invoke as an update in the event

Mike Fellows | Senior OSS Specialist

Direct: 07701319753 | Mobile: 07701319753

E-mail: Mike.Fellows@telent.com | Web: www.telent.com<http://www.telent.com>

telent Technology Services Ltd. Registered in England. No 703317.

Registered Office: Point 3, Haywood Road, Warwick, CV34 5AH, England

telent Technology Services Limited is authorised and regulated by the Financial Conduct Authority for credit-related regulated activities

telent_new_logo1.png

fellowsm

Hello,

This runs in SWQL but still doesn’t return a NULL when the output is null

SELECT ISNULL(count(*),0) as Comments

FROM Orion.NPM.Interfaces

where (Interfaces.AdminStatus = 1)

and (Interfaces.NodeId = 2596 )

and (Interfaces.InterfaceAlias like '%ACCS%' )

group by Nodeid}

In the alarm it just fills the custom variable with the SWQL

See attached screenshot

Mike

Mike Fellows | Senior OSS Specialist

Direct: 07701319753 | Mobile: 07701319753

E-mail: Mike.Fellows@telent.com | Web: www.telent.com<http://www.telent.com>

telent Technology Services Ltd. Registered in England. No 703317.

Registered Office: Point 3, Haywood Road, Warwick, CV34 5AH, England

telent Technology Services Limited is authorised and regulated by the Financial Conduct Authority for credit-related regulated activities

attachments.zip

dquerry

Can you not use define SQL/SWQL Variable? See the attached screenshot. Otherwise what about a custom script?

fellowsm

This is what I thought I was doing – the SWQL/SQL just doesn’t work when there is a 0 count

Mike Fellows | Senior OSS Specialist

Direct: 07701319753 | Mobile: 07701319753

E-mail: Mike.Fellows@telent.com | Web: www.telent.com<http://www.telent.com>

telent Technology Services Ltd. Registered in England. No 703317.

Registered Office: Point 3, Haywood Road, Warwick, CV34 5AH, England

telent Technology Services Limited is authorised and regulated by the Financial Conduct Authority for credit-related regulated activities

telent_new_logo1.png