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

Query works in SWQL but not in SWIS

Hey all,

I been trying to build a powershell script using Orion SDK that finds all IP addresses that are available between two subnets.

When I create the query in SWQL studio it works!

SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2')

But when I use "get-SwisData" it fails and I get a big array that contains "PscxDynamicType0" over and over. Below is my script and the array I am trying to walk through is "$objSwisData".

#Region PSSnapin presence check/addif (!(Get-PSSnapin -Name "SwisSnapin" -ErrorAction SilentlyContinue)){        Add-PSSnapin SwisSnapin -ErrorAction SilentlyContinue}#EndRegion$target="Server"$swis = Connect-Swis -host $target -UserName "username" -Password "password"$objSwisData = Get-SwisData -SwisConnection $swis -Query "SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2' )"foreach($nodeSwisData in $objSwisData){  $counter++  Write-host $nodeSwisData.IPAddress  }}

Let me know if anyone has run into something similar.

Thanks,

Dustin

Find more posts tagged with

sdk_powershell

swql_studio

Accepted answers

muralikvp

Try to use this below query

==========================

SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I inner join IPAM.GroupNode g on (i.Subnetid = g.Groupid)

Where i.Status=2 AND (g.FriendlyName = '<Your Friendly Name>' OR g.FriendlyName = '<Your Friendly Name>') and Distance =1

======================

Replace <Your Friendly Name> with your subnet names.

Earlier your query has entity IPAM.IPNode which will create duplicate values of IPAddress with no of accounts in IPAM. That means if you have 5 accounts in IPAM then 5 IPAddress with same value will be duplicated instead of having one.

This kind of behavior for an entity IPAM.IPNode will be handled programmatically. For customer usage one should add inner join IPAM.GroupNode in where condition to avoid such duplicates in result.

While connecting SWQL studio try to select 'Orion (v3)' in servertype option. Orion (v2) is depreciated.Feel free to ask if you have any clarifications on above mentioned query

All comments

tdanner

A collection of "PscxDynamicType0" objects is normal. Get-SwisData creates powershell objects at runtime with properties that match whatever columns get returned by the swis query.

When you say that it "fails", what error are you seeing?

dusting

When I assign my query to the array

objSwisData = Get-SwisData -SwisConnection $swis -Query "SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2')"

The array/object gets a Type name of PscxDynamicType0 and 3 properties that I selected in my query IPAddress, DnsBackward, and DhcpClientName.

When I loop through these objects in the array I will get 50 of the same IP before it move to the next. When I query from SWQL Studio I only see 1 IP per line where I see 50 of the same IPs when looping through the foreach.

Here is my example. You can see the $objSwisData comes with a collection of PscxDynamic objects with the count of 1862 and it should be 38.

Also,

I have done this same task with other queries with swis and have not seen this issue.

Thanks,

Dustin

tom.rybka

How do you authenticate in SWIS in PS and in SWQL? Are you using an account (user/password) or the Orion certificate?

dusting

Both with same account. I am getting data but it is just repeated 50x.....

muralikvp

Try to use this below query

==========================

SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I inner join IPAM.GroupNode g on (i.Subnetid = g.Groupid)

Where i.Status=2 AND (g.FriendlyName = '<Your Friendly Name>' OR g.FriendlyName = '<Your Friendly Name>') and Distance =1

======================

Replace <Your Friendly Name> with your subnet names.

While connecting SWQL studio try to select 'Orion (v3)' in servertype option. Orion (v2) is depreciated.Feel free to ask if you have any clarifications on above mentioned query

dusting

Muralikvp,

Your query displayed the desired results. Interestingly enough, my query would work with Orion (v2) but not Orion (v3).

Thanks for the help!

Dustin