This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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/add

if (!(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

  • 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?

  • 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.

    pastedImage_5.png

    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.

    pastedImage_6.png

    Also,

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

    Thanks,

    Dustin

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

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

  • Can you please grab SWIS log files in the debug message level for me to look at? You can change the logging level for SWIS using the LogAdjuster tool installed with Orion (changing the SWIS / Information Service 3.0 category is sufficient).

    Having changed the logging level, please grab the log file (typically C:\ProgramData\Solarwinds\InformationService\v3.0\InformationService.log) after executing the query in SWQL and after executing the PS script. Also, please, restart the SWIS 3.0 service before executing the query to avoid applying cached query plan in SWIS.

  • Tom,

    Since we will be restarting the service it affect production systems so we will have to do this early in the morning. Also, we created a case #819907 since we will be sending logs and would rather avoid posting to the message board.

    Thanks,

    Dustin

  • Ok, makes sense, I will make sure I get to the data.

    One more suggestion - the restart is not necessary if you make sure that there is no cached query plan to be used. One trick to do that is: execute a query with added multiple whitespace characters in a query, so the query text is unique (no such query text has been sent to SWIS yet). This way you can achieve the same effect of complete query processing as with the restart.

  • 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.

    1.png

    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

  • 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