Updated Function: Get-SQLInstance

One function of mine that I use quite a bit to get information about SQL servers is Get-SQLInstance not only because it provides most of the information that I am looking for but it also is a great way to scan for all of the SQL servers on my network when combined with an Active Directory query to grab all of the servers on the domain.

This was a completely registry-centric tool that used different parts of the registry to gather a decent amount of information without having to actually connect to the SQL server itself and running some TSQL commands to learn more about the system. Now by looking at WMI, I can grab even more information while still not worrying about making a connection and running TSQL.

WMI, as you may or may not know, is an excellent repository of data that exists in Windows and lets you query for just about anything as long as you know where to look. Of course, using PowerShell means that exploring WMI is as simple as running some Get-WMIObject commands using either –List or (if you already know the class) connecting directly to the class using –Class and seeing what information is sitting there waiting to be discovered!

Some of the information that I need requires me to do a little digging around. I have to look at the Root\Microsoft\SQLServer namespace and then taking that information to then look at and then after I find what I need, I can then dig a little deeper into the namespace to unearth more information about the SQL server.

$WMIParams=@{
    Computername = $Computer
    NameSpace='root\Microsoft\SqlServer'
    Query="SELECT name FROM __NAMESPACE WHERE name LIKE 'ComputerManagement%'"
    Authentication = 'PacketPrivacy'
    ErrorAction = 'Stop'
}

I already know what properties I am looking for, so I am going to build a hash table with empty values that I can then use later on for adding items to it. This way, if there are no values added, it will still have the property names.

$PropertyHash = [ordered]@{
    Computername = $Computer
    Instance = $Null
    SqlServer = $Null
    WmiNamespace = $Null
    SQLSTATES = $Null
    VERSION = $Null
    SPLEVEL = $Null
    CLUSTERED = $Null
    INSTALLPATH = $Null
    DATAPATH = $Null
    LANGUAGE = $Null
    FILEVERSION = $Null
    VSNAME = $Null
    REGROOT = $Null
    SKU = $Null
    SKUNAME = $Null
    INSTANCEID = $Null
    STARTUPPARAMETERS = $Null
    ERRORREPORTING = $Null
    DUMPDIR = $Null
    SQMREPORTING = $Null
    ISWOW64 = $Null
    BackupDirectory = $Null
    AlwaysOnName = $Null
}

Now we can start out with our query to first find out the namespace that I want to connect to and then to start looking at the SQL properties using a different filter. In this case SqlServiceType = 1 means that I am only looking for the  SQL Server service (more info on that here).

$Namespace = (Get-WMIObject @WMIParams).Name
$Filter = "SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType=1" 
$PropertyHash['WMINamespace'] = $Namespace
$WMIParams.NameSpace="root\Microsoft\SqlServer\$Namespace"
$WMIParams.Query=$Filter
$WMIResults = Get-WMIObject @WMIParams

The results of this query provides us with a nice amount of information in both a string and number format.

image

From here it is a matter of adding each of these to their respected name in the hash table and then displaying the results at the end.

$WMIResults | ForEach {
    $Name = "{0}{1}" -f ($_.PropertyName.SubString(0,1),$_.PropertyName.SubString(1).ToLower())    
    $Data = If ($_.PropertyStrValue) {
        $_.PropertyStrValue
    }
    Else {
        If ($Name -match 'Clustered|ErrorReporting|SqmReporting|IsWow64') {
            [bool]$_.PropertyNumValue
        }
        Else {
            $_.PropertyNumValue
        }        
    }
    $PropertyHash[$Name] = $Data
}

The end result is some nice additions to my function that you can see below.

Get-SQLInstance –Computername vSCOM

image

Be sure to download the updated function below and if you want to help make this better, be sure to fork my repo and submit a pull request!

Download the updated Get-SQLInstance

https://gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

Help contribute to Get-SQLInstance

https://github.com/proxb/PowerShell_Scripts

Posted in powershell | Tagged , , , | 2 Comments

A Look at a RegExHelper Tool for PowerShell Regular Expressions

I don’t know about you, but writing a regular expression can sometimes be a lesson in patience when putting a pattern together to parse a log file or provide some sort of validation of input. Each time I need to write a RegEx pattern, I tend to find myself taking a string that I want to use as an example and then going to another ISE tab and begin working out the details of the pattern to ensure that it works like I want it to work. Given, this isn’t really that bad to do, but I wanted a way to show the results as I am working on the pattern so I can knock out what I am looking to do.

With that, I decided to write a UI using WPF (using XAML as the front end) that that would meet my requirements of having a place to input the string that I wanted to write a RegEx for and then another text box that would be used for my RegEx pattern. The next part is the DataGrid that would display the matches as I begin writing the pattern. Lastly, because I wanted to provide all of the options that are available when using RegEx for .Net, there is a pane at the very bottom that gives you all of the options like what you see below:

[System.Text.RegularExpressions.RegexOptions].GetEnumNames()

image

More information about these can be found at the following link: https://msdn.microsoft.com/en-us/library/yd1hzczs(v=vs.110).aspx

My main project for the RegEx Helper is here: https://github.com/proxb/RegExHelper. This UI only has a single tab which is used for checking a string to find a match.

This does run as a function, so you will need to dot source the script file to load the function into the console.

. .\Invoke-RegExHelper.ps1

Invoke-RegExHelper

RegExHelper

I do have a Dev branch that has a version of this project that includes another tab that allows you to put a log file in (or part of a log) so you can write a pattern that can parse out a specific item or items and will highlight the results when you click on the Hightlight button. I haven’t made this part of the Master branch as I want to solve the performance issues when the size of the log is large and the number of matches being highlighted are fairly large as well. This is where I hope to have some community participation to help iron out this issue to really make this a great tool. The link to that branch is here: https://github.com/proxb/RegExHelper/tree/Dev

image

Anyways, I hope that you find this useful and if you want to help contribute to it, then you know what to do! Fork the repo and do a Pull Request of course! Smile

I plan to write a follow up post on just how I built this and my thought process on the design, but today I just wanted to get it out in the open and start gathering feedback on this tool.

Posted in powershell | Tagged , , , | 1 Comment

Performing an Asynchronous DNS Lookup Using PowerShell

This is another asynchronous approach to a common thing that a system administrator might do, much like a previous article I did on doing a speedy ping with PowerShell and async methods. I’ve already covered performance as well as handling each Task object so that won’t be covered here. Please review the linked blog for more information about those topics.

Instead of a ping, I am going to perform a lookup against DNS for an IP or hostname to get more information about a particular system or systems. The type that I am using to perform the DNS lookups is System.Net.Dns and the methods that I use are GetHostAddressesAsync() and GetHostEntryAsync().

Using both of these allows me to specify either an IPaddress or a hostname to perform the lookup against. Each item passed is tested to see if it is an IP address or not and then passed to the appropriate method that will be called with the data. Once that is done, it is passed to the Task watcher and then when everything has completed, you are presented with the results of each lookup.

The results of the command look something like this:

Get-DNSHostEntryAsync -Computername google.com,prox-hyperv,bing.com, github.com, `
powershellgallery.com, powershell.org,216.58.218.142

SNAGHTML87ff5b7

If you wanted to make this better, it is out on my GitHub repo so feel free to fork it and submit a PullRequest! Otherwise let me know what you think of it and enjoy!

Download Get-DNSHostEntryAsync

https://gallery.technet.microsoft.com/scriptcenter/Get-DNSHostEntryAsync-6fbd006c

Posted in powershell | Tagged , , | 3 Comments

Quick Hits: Create and Start a Stopwatch in One Line

Ok, so this is a pretty trivial thing but I happened to stumble across this while doing some work on figuring out a bottleneck in one of my scripts.

Essentially, I set up a StopWatch to see how long it takes to complete a chunk of code and as you may or may not know, you usually set this up by instantiating the object using New-Object and then using the Start() method to kick it off, like this:

$StopWatch = New-Object System.Diagnostics.Stopwatch
$StopWatch.Start()
$StopWatch.Elapsed
$StopWatch.Stop()

We see the object being created, then started and stopped as well as viewing the elapsed time.

image

Ok, not really a lot of code here, but I’m always curious to see how much shorter the code could go, and sure enough, there is a static method on the type called StartNew() which not only creates the object but also starts the stopwatch for you. Now all you have to do is monitor the elapsed time and stop it when you are done with it.

$StopWatch = [System.Diagnostics.Stopwatch]::StartNew()
#Wait a bit
$StopWatch.Stop()
$StopWatch.Elapsed

image

Nothing spectacular here or life changing, but just a slightly shorter way to perform this particular task. Enjoy!

Posted in powershell | Tagged , , | Leave a comment

Speedy Ping using PowerShell

For those that know me, you know how much I love runspaces and preach about their use for their quickness and being lightweight in PowerShell to accomplish your day to day activities where you need to run many things in parallel and at the same time want to throttle how many things run at a time. In fact, I loved them so much that I made a module to make the process easier for everyone else!

Anyways, one of the most common things that a Sysadmin might do is use ping or Test-Connection to check if a system is online (or at least is allowed to send ICMP responses back) so that you can perform additional work or document that the system is still available. This works great, but what if you wanted to ping many systems at a time to figure out their availability? Most likely you would grab a collection of systems and work through each one to check with Test-Connection.

In fact, with Test-Connection you have the –AsJob parameter which pushes all of the work to a single PSJob that checks all of the systems and returns completed when all of the systems have been checked. Note that you need remoting configured on those systems. This is actually a pretty fast approach to performing a large scale ping of your systems. In my testing I found it to be at or near the top on a number of iteration variations.

image

Test-Connection -ComputerName google.com,prox-hyperv -Count 1 -AsJob | 
Wait-Job | Receive-Job

image

Another approach is to use a runspacepool with runspaces to provide some multithreading to attempt to ping more than one system at a time. I won’t cover using Start-Job as history shows that they are heavy and runspaces perform much faster than PSJobs. In my example, I use my module, PoshRSJob with Test-Connection in an attempt to get the most impact from runspaces.

Lastly, and the technique I am starting to use more and more is the SendPingAsync Method of the System.Net.NetworkInformation.Ping class which sends the work to another Task object in which the operation is performed in the background and you can check to see if it is completed after a while or wait on the object using [Threading.Tasks.Task]::WaitAll(). An example of doing this is below:

$Task = (New-Object System.Net.NetworkInformation.Ping).SendPingAsync('prox-hyperv')
$Task

image

Here I am creating the System.Net.NetworkInformation.Ping class and then immediately calling SendPingAsync() while supplying the name of the system that I want ping. The object returned is a

System.Threading.Tasks.Task`1[[System.Net.NetworkInformation.PingReply, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] object. Yea, that is quite the object name and you will notice that it is strongly typed for the particular object (System.Net.NetworkInformation.PingReply) that is returned from the Ping but wrapped in the System.Threading.Tasks.Task.

Digging more into this object, we can look at the results in the result property.

$Task.Result

image

In this case, our result of successful and we even have the returned IP address. Also note that by the time I displayed the $Task after saving the object to it, the command was already done. If there was some sort of failure, it would have looked like this:

image

Definitely a different look here. There is nothing in the Result property and the error provides a lot of data with the most important piece being that “No such host is known”. What is unfortunate here is that there is no record of the system that this ping was attempted with. This can make it more difficult if you decided to use multiple systems with your ping async attempt. But the tradeoff for this is its amazing speed by use of the Async method.

Being the curious person that I am, I wanted to run a performance test using these approaches and see how they stacked up. The methods that I am using are:

  1. SendPingAsync
  2. Test-ConnectionAsync
    1. Wrapper for the SendPingAsync method that provides the computername tested; download here.
  3. PoshRSJob for runspaces
  4. Test-Connection using –AsJob
  5. Test-Connection within a ForEach () {} loop

The code that I am using is below and it covers what I feel are the most likely checks for online systems.

1,10,100,500,1000,5000 | ForEach {
    Write-Verbose "Iteration: $_" -Verbose
    $Computername = (1..$_).ForEach({'prox-hyperv'})
    $Time = (measure-command {
        $Task = forEach ($Computer in $Computername) {
            (New-Object System.Net.NetworkInformation.Ping).SendPingAsync($Computer)
        }
        [Threading.Tasks.Task]::WaitAll($Task)
        $Task.Result
    }).TotalMilliseconds
    [pscustomobject]@{
        Type = 'SendPingAsync'
        Count = $_        
        Time_MS = $Time
    }

    $Time = (Measure-Command {
        $Computername|Start-RSJob {Test-Connection -ComputerName $_ -Count 1} -Throttle 5 |
        Wait-RSJob | Receive-RSJob
    }).TotalMilliseconds
    [pscustomobject]@{
        Type = 'RSJob'
        Count = $_        
        Time_MS = $Time
    }

    $Time = (Measure-Command {
        Test-ConnectionAsync -Computername $Computername
    }).TotalMilliseconds
        [pscustomobject]@{
        Type = 'Test-ConnectionAsync'
        Count = $_        
        Time_MS = $Time
    }

    $Time = (Measure-Command {
        ForEach ($Computer in $Computername) {
            Test-Connection -ComputerName $Computer -Count 1 
        }
    }).TotalMilliseconds
        [pscustomobject]@{
        Type = 'Test-Connection:ForEach'
        Count = $_        
        Time_MS = $Time
    }

    $Time = (Measure-Command {
        $Job = Test-Connection -ComputerName $Computername -AsJob -Count 1 
        $Job | Wait-Job | Receive-Job
    }).TotalMilliseconds
        [pscustomobject]@{
        Type = 'Test-Connection:AsJob'
        Count = $_        
        Time_MS = $Time
    }

    Get-Job|Remove-Job
    Get-RSJob|Remove-RSJob
}

I found that eventually Test-Connection fails me with the –AsJob parameter as the list of machines gets larger (besides slowing down some). Turns out that this was reported out on User Voice as a possible bug, so I encourage you to vote on this if you agree with me. That link is here: https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/11087637-test-connection-throws-quota-exception-when-passedSNAGHTML1a343600

Now because of this, the results are going to be off for Test-Connection, but as I mentioned earlier, the results were starting to drift from being the fastest to being more to the middle of the pack.

image

image

image

image

image

I am ignoring Test-Connection:AsJob as it failed with a Quota Violation in the final two sets of results.

image

image

So the results look rather interesting. The smaller batches show that using Test-Connection with the –AsJob parameter performed right along with the SendPingAsync approaches. More interesting is that using runspaces really didn’t compete at all with those approaches. As we got into the 500 count batch, the SendPingAsync began to separate itself from the Test-Connection –AsJob and the final two tests were inconclusive for –AsJob as it met quota violations while the Async approaches kept on marching with some great times compared to everything else. My assumption is that it would have beat Test-Connection –AsJob.

So what does this all mean? Well, it means that most of the time, you could get by with Test-Connection using AsJob assuming that your systems have remoting enabled. But if you wanted to quick way to look at hundreds of IPs or hostnames, then using the Async approach will not fail you.

Feel free to give my code a try and let me know what you find out in the comments below!

Posted in powershell | Tagged , , , | 2 Comments