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

SWQL query via SwisPowerShell module fails to resolve hostname property

I'm trying to figure our why this code that once worked is now failing for me. Did I accidentally change something? When running, the error is "Get-SwisData : Cannot resolve property Hostname". (The PatchWeek and EMSSGuid values are my custom properties.) The weird thing is, there is no error at all when initiating the Swis connection.

# Splat all the SwisConnection parameters and then connect with $swis
$SwisParams = @{
    Hostname = 'SERVERFQDN';
    Username = 'USERNAME';
    Password = 'PASSWORD';
}
$swis = Connect-Swis @SwisParams

# Get all Windows nodes from Orion with their hostname, entity URI, PatchWeek, and EMSSGuid value. 
$query = @"
SELECT n.Caption AS [Name], n.Uri AS [EntityUri], ncp.PatchWeek AS [PatchWeek], ncp.EMSSGuid AS [Guid]
FROM Orion.Nodes n
JOIN Orion.NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
WHERE n.Vendor LIKE 'Windows'
ORDER BY Hostname ASC


$OrionNodes = Get-SwisData -SwisConnection $Swis -Query $query

Get-SwisData : Cannot resolve property Hostname

At script.ps1:59 char:15

+ $OrionNodes = Get-SwisData -SwisConnection $Swis -Query $query

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : InvalidOperation: (:) [Get-SwisData], FaultException`1

+ FullyQualifiedErrorId : SwisError,SwisPowerShell.GetSwisData

Find more posts tagged with

Accepted answers

antonis.athanasiou

Hello!

on your query, you are using 'Hostname' on your ORDER BY, but you are not selecting it in the SELECT statement (neither aliasing it). Maybe try 'ORDER BY Name' ?

All comments

antonis.athanasiou

Hello!

on your query, you are using 'Hostname' on your ORDER BY, but you are not selecting it in the SELECT statement (neither aliasing it). Maybe try 'ORDER BY Name' ?

sturdyerde

[facepalm] You nailed it. Forgot that I changed the hostname alias in my select statement to make a later Compare-Object statement easier. Failed to change my ORDER BY statement. Thanks for the second pair of eyes, antonis.athanasiou!

antonis.athanasiou

No problem! been there too many times myself, its the first one to notice nowadays!

clarv02

hey sturdyerde, I noticed in your query the sql JOIN statement and I wanted to let you know about a way to avoid doing JOINs with SWQL. There are many built-in "links" (actually represented by chain link icons) in SWQL Studio). Here is basically the equivalent SWQL query to yours, but with a couple of changes. First I had to change it to work in my environment, so custom property names are different than yours (and I ordered by Caption). The other change is removing the JOIN and adding the "link" to those custom properties:

SELECT n.Caption AS [Name], n.Uri AS [EntityUri], n.CustomProperties.Org AS [Organization], n.CustomProperties.Business_Unit

FROM Orion.Nodes n

WHERE n.Vendor LIKE 'Windows'

ORDER BY Caption ASC

Once I got used to doing this, since then I always look for those "links" when I need relational data from other tables. Sometimes the link for what I want does not exist, and I have to do the join. But I've been able to eliminate joins from much of my code since learning how to do this. Here is a great article that walks us through how this works:

SWQL Walkthrough

sturdyerde

Thank you very much, clarv02! I will try this out ASAP.