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
Store

SQL Query is not null

hpstech

I'm trying to just pull Cisco devices for this query and drop the null values - not working though - any ideas here?

I've tried variations but they don't work, the single quotes brings in data - no quotes gets an error.

Trying what I found here, but no bueno so far.

thwack.solarwinds.com/.../report-sql---how-to-display-only-if-field-is-not-null

Find more posts tagged with

Accepted answers

Kuroneko

This is how I would do it with is not null. *NOTE* - I am not a DBA, but a net admin who learned how to SQL to play with solarwinds.

I am using vendor as a filter, you can also use machine type as an identifier for Cisco. If you want to use custom properties, that would require another JOIN. A lot easier if you use a report and SWQL.

SELECT
N.Caption,
PS.Status AS [SerialNumber]

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
LEFT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE N.Vendor LIKE '%Cisco%' AND CPA.AssignmentName LIKE '%Ser%' AND PS.Status IS NOT NULL
ORDER BY N.Caption

Alternatively you can skip the not null with a RIGHT JOIN.

SELECT
	N.Caption,
	PS.Status AS [SerialNumber]

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
RIGHT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE N.Vendor LIKE '%Cisco%' AND CPA.AssignmentName LIKE '%Ser%'
ORDER BY N.Caption

All comments

Kuroneko

Is it possible to put in the full query. Some of that inner join seems unnecessary and I have no idea if you are applying the where is not null correctly. So many where.

hpstech

This is a query (serial numbers) I found on Thwack to report on Cisco serial numbers which is a nightmare in itself.

This query also doesn't work using other tools such as SNOW SQL API but does work using Orion DB connection manager - its driving us all nuts.

Kuroneko

This is how I would do it with is not null. *NOTE* - I am not a DBA, but a net admin who learned how to SQL to play with solarwinds.

SELECT
N.Caption,
PS.Status AS [SerialNumber]

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
LEFT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE N.Vendor LIKE '%Cisco%' AND CPA.AssignmentName LIKE '%Ser%' AND PS.Status IS NOT NULL
ORDER BY N.Caption

Alternatively you can skip the not null with a RIGHT JOIN.

SELECT
	N.Caption,
	PS.Status AS [SerialNumber]

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
RIGHT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE N.Vendor LIKE '%Cisco%' AND CPA.AssignmentName LIKE '%Ser%'
ORDER BY N.Caption

hpstech

Thank you very much for the queries!

Works great!

Kuroneko

Can you run this? I'm using the original post's poller ID instead of searching by Cisco.

SELECT
N.Caption,
PS.Status AS [SerialNumber],

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
LEFT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE CPA.PollerID = '6554a591-5a27-4ba7-91de-4fd89a0cf8ff' AND PS.Status IS NOT NULL
ORDER BY N.Caption

If you are curious what is being polled, I added descriptions to my original recommendation

SELECT
N.Caption,
PS.Status AS [SerialNumber],
CPA.AssignmentName AS [Description],
CPA.CustomPollerID

FROM NodesData N

LEFT JOIN CustomPollerAssignment CPA ON N.NodeID = CPA.NodeID
LEFT JOIN CustomPollerStatus PS ON CPA.CustomPollerAssignmentID = PS.CustomPollerAssignmentID

WHERE N.Vendor LIKE '%Cisco%' AND CPA.AssignmentName LIKE '%Ser%' AND PS.Status IS NOT NULL
ORDER BY N.Caption