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

Having an issue pulling data from two tables, normally I use a Join. This time I was trying this method of swql linked tables. It is throwing a common error but I cannot find it this time.

Having an issue pulling data from two tables, normally I use a Join. This time I was trying this method of swql linked tables. It is throwing a common error but I cannot find it this time.
error: Column is invalild in the select list cause it is not contained in the either the aggregate function or the GROUP BY clause.

SELECT [NN].Caption,
[NN].Location,
[NN].IOSImage,
[NN].MachineType,
[NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model as Model
FROM Orion.Nodes as [NN]
WHERE [NN].Vendor like '%cisco%' and not ([NN].MachineType like '%Cisco Unified Communications Manager%' or [NN].MachineType like '%Identity Services Engine%' )
GROUP BY [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model
HAVING [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model NULL
ORDER BY [NN].Caption,
[NN].Location,
[NN].IOSImage,
[NN].MachineType,
[NN].Vendor

Find more posts tagged with

swql query

SWQL

Accepted answers

All comments

KMSigma.SWI

Does just the SELECT.. FROM portion work?

SELECT [NN].Caption,
[NN].Location,
[NN].IOSImage,
[NN].MachineType,
[NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model as Model
FROM Orion.Nodes as [NN]

That's the first step.

wysockin

Yes it does, all 5 columns return.

In the meantime I used the Dynamic Query Builder for an almost identical search. This does run but the output of the Dynamic Query Builder confirms the error message above. I am getting a lot more data then I should have; most of it is duplicated. For every of Node I am getting a copy of HardwareInfo.Model.

KMSigma.SWI

Regarding syntax:

"HAVING [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model NULL" is incorrect

"HAVING [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model IS NULL" is correct.

I'm sorry, but I don't have a lab where I have data and can test this at the moment.

wysockin

Thank you for the correction. With

HAVING [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model IS NULL

I receive the error: Having expression requires a binary expression

What I had originally was

HAVING [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model <> ''

ecmel

Is there any reason for grouping the data, as I couldn't see any data aggregation definition in column definitions?

If you just want to list nodes that have hardware model information, then you can change your query like this:

SELECT [NN].Caption,
[NN].Location,
[NN].IOSImage,
[NN].MachineType,
[NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model as Model
FROM Orion.Nodes as [NN]
WHERE [NN].Vendor like '%cisco%' and not ([NN].MachineType like '%Cisco Unified Communications Manager%' or [NN].MachineType like '%Identity Services Engine%' )
AND [NN].HardwareHealthInfos.HardwareItem.HardwareInfo.Model <>''
ORDER BY [NN].Caption,
[NN].Location,
[NN].IOSImage,
[NN].MachineType,
[NN].Vendor