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

Basic (very) Powershell Queries

So I've watched and re-watched Episode 86 - Intro to Powershell and Orion API - by @KMSigma.SWI and whilst some light is starting to become apparent, I'm still very much clueless.

So, I have managed to create my Credentials and my Connection, and furrther, I have tested these as working.

Originally, I tried to copy exactly what Kevin was doing in the video but kept getting an error stating that the 'from keyword' was not supported, but it clearly was as Kevin was running it just fine. I read and re-read my code but couldn't see what was wrong and so in the end just went with a very basic query.

$SwqlQuery = "
SELECT TOP 10 Caption
FROM Orion.Nodes"

Which worked - but not very useful for anything, other than for me to start to get to grips with this. So, my first (self) challenge was to get it so I could see what this output is, but for the life of me I can't workout:

How do I view the resultant output on the screen? ...or
How do I view that same output, but exported to a file (for example)
- though I did find 'Out-File' cmdlet and used: Out-File -FilePath d:\test.txt but the file itself was empty.

The next query is to try and understand a part of the code Kevin had... at 42m in, the code shows: $SwqlQuery = @ code snippet @ but I'm missing the understanding of the @';s in the code.

I may be trying to run here before I can walk so I am also off to do some more reading, if I can find something appropriate to link together the use of the Swis stuff with Powershell.

Find more posts tagged with

Accepted answers

mrxinu

You're very welcome! So as for why that line is necessary, let's break it down.

Original Line:

$Results = Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQuery

Explanation of each element:

$Results
1. Variables in PowerShell start with a $ sign. This variable is called $Results and whatever answer comes back from SolarWinds when you provide the query goes into this variable. In this case, with a query like "SELECT Caption FROM Orion.Nodes", the $Results variable would be a list of captions.
=
1. The equal sign does assignment. Everything that happens on the right side gets assigned to the variable on the left side. It works the same as X = 5 + 7. X will be equal 12 because we placed it on the left side of the equal sign.
Get-SwisData
1. This is called a cmdlet (pronounced command-leht). In PowerShell, these are the commands that we issue when we want something. You want a list of files, you type Get-ChildItem (very strange name, I know). You want a list of services, you type Get-Service. You want a list of process, you type Get-Process. The Verb-Noun format is common and in most cases PowerShell will complain at you if you try to do something clever like Make-Things-Happy. First it has too many pieces and second it doesn't match their Verb-Noun view of the world.
2. In our case, the cmdlet Get-SwisData gets us information from SolarWinds. The name only makes sense if you know the underpinnings of SolarWinds is called the SolarWinds Information Service or SWIS. So GetSwisData gets you data from the SWIS (or SolarWinds).
-SwisConnection $SwisConnection
1. To use the Get-SwiData cmdlet you need to offer it information about how to connect to your system. When you created the $SwisConnection variable higher up in the script you gave it a hostname and some credentials (trusted if you wanted to use your currently logged-in AD account, username & password if you wanted to do it in plain text).
-Query $SwqlQuery
1. Finally, this is what holds your query. You're telling the Get-SwisData cmdlet "this is the query I want to run" by saying -Query which is the option name and then $SwqlQuery which contains your query. That was configured a few lines higher in the script.

All comments

stuartd

Talking to myself @stuartd as I find I'm usually the only one that listens to me

Anyway, have done some reading and found out some of my answer, I think, but don't really understand it. If I add:

Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQuery

to the end ... then I get my output on screen. So, success. YAY. But still can't suss out the out-file part.

50% of the way there, though not understanding the first 50% fully!!

mrxinu

Hey @stuartd, so two things. First the <at sign><quote><multi-line text><quote><at sign> is something you used to have to do in earlier versions of PowerShell to make a multi-line string (the query) work. Now you can just put quotes at the beginning and end and be good.

$query = "
  SELECT
    Caption
    , IPAddress
  FROM Orion.Nodes
"

As for the Out-File, that should be working if you pipe the Get-SwisData command to Out-File with the -FilePath option. Can you post your entire script so I can have a look at it?

*Note: I've had to move to UTC to teach a class this week so I'm about to head to bed. I'll check this thread when I get up in ~7 hours.

stuartd

@mrxinu- I'm grateful to anybody that replies, no matter how slow, so thank you.

My code is,very simple because nothing else was working I thought, let's pare it back to the basics. So in SWQL Studio, I run the following and get back a list of node names, no problems.

SELECT
    Caption
  FROM Orion.Nodes

Taking Kevin at his statement in the video that (paraphrased): "you get it working in SWQL Studio first, then copy to Powershell" so I did just that and added the $SwqlQuery = " <code> " a la:

$SwqlQuery = "SELECT
    Caption
  FROM Orion.Nodes
"

And I get nothing back at all - until I discovered I needed to add the 'Get-SwisData etc' line [not that I know what this is doing]

Emboldened by this I wanted to export to a file so created this:

$SwqlQuery = "SELECT
    Caption
  FROM Orion.Nodes
"
Out-File -FilePath e:\test.txt

Which created a 1kb empty file called test.txt

KMSigma.SWI

The @ @ formatting is called a here-string.

What you need to understand about PowerShell is that each cmdlet does one thing.

So if you want to the output from a specific query in a flat file this is the process.

Get the results of a query a variable:
$Results = Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQuery

Take those results and output them to a file.

$Results | Out-File -FilePath C:\Scripts\Output.txt

KMSigma.SWI

Also, for "formatted" output, I would recommend exporting to a CSV file. It's easier to work with if you need to later re-import something.

$Results | Export-CSV -Path C:\File\Output.csv -NoTypeInformation

mrxinu

So you had a query string set up and then you used Get-SwisData and then you tried to use Out-File without any use of Get-SwisData. I think wires might be getting crossed. Here's a fully-functioning script that does exactly what you want. Adjust the $hostname, $username, and $password variables accordingly. The resulting file should be called results.txt and be in the same folder where the script is running from.

# import the module
Import-Module SwisPowerShell

# set up connection details
$hostname = "localhost"
$username = "admin"
$password = ""

# create a connection to SolarWinds Information Service (SWIS)
$swis = Connect-Swis -Hostname $hostname -Username $username -Password $password

$query = "
    SELECT
        Caption
    FROM Orion.Nodes
"

$rows = Get-SwisData -SwisConnection $swis -Query $query

$rows | Out-File -FilePath "results.txt"

stuartd

@KMSigma.SWIand @mrxinu

Thank you both.

I realise I am almost certainly trying to run before I walk with this. If there is one thing I have discovered, is that I learn best is by having a working example (no matter how basic) that I've created (even if heavily copied) and can then walk back through to link to harder to understand (for me) use cases.

For now, what I need to get my head around is why "$Results = Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQuery" is necessary, and what each part of the string does or why it is needed.

But so far, I've now taken a SWQLStudio query I have to retrieve our SNMP v3 details (some of our change engineers are poor at documenting stuff) and converted it into a Powershell script. And what's more, it works Now off to try exporting it to a file...

THANKS again.

Again, THANK YOU.

mrxinu

You're very welcome! So as for why that line is necessary, let's break it down.

Original Line:

$Results = Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQuery

Explanation of each element:

$Results
1. Variables in PowerShell start with a $ sign. This variable is called $Results and whatever answer comes back from SolarWinds when you provide the query goes into this variable. In this case, with a query like "SELECT Caption FROM Orion.Nodes", the $Results variable would be a list of captions.
=
1. The equal sign does assignment. Everything that happens on the right side gets assigned to the variable on the left side. It works the same as X = 5 + 7. X will be equal 12 because we placed it on the left side of the equal sign.
Get-SwisData
1. This is called a cmdlet (pronounced command-leht). In PowerShell, these are the commands that we issue when we want something. You want a list of files, you type Get-ChildItem (very strange name, I know). You want a list of services, you type Get-Service. You want a list of process, you type Get-Process. The Verb-Noun format is common and in most cases PowerShell will complain at you if you try to do something clever like Make-Things-Happy. First it has too many pieces and second it doesn't match their Verb-Noun view of the world.
2. In our case, the cmdlet Get-SwisData gets us information from SolarWinds. The name only makes sense if you know the underpinnings of SolarWinds is called the SolarWinds Information Service or SWIS. So GetSwisData gets you data from the SWIS (or SolarWinds).
-SwisConnection $SwisConnection
1. To use the Get-SwiData cmdlet you need to offer it information about how to connect to your system. When you created the $SwisConnection variable higher up in the script you gave it a hostname and some credentials (trusted if you wanted to use your currently logged-in AD account, username & password if you wanted to do it in plain text).
-Query $SwqlQuery
1. Finally, this is what holds your query. You're telling the Get-SwisData cmdlet "this is the query I want to run" by saying -Query which is the option name and then $SwqlQuery which contains your query. That was configured a few lines higher in the script.