PowerShell Server Inventory, Part 1: SQL Database Build

If you’re like me, you might not have SCOM or SCCM or some other 3rd party solution that you can leverage to track all of your systems in your environment that you can pull up at a glance to know what software is on it or what local account might be created on it. Fortunately, with the combined forces of PowerShell and SQL, we can build up our own solution that can provide us this information. This is where I came up with a project to roll my own server inventory system.

ServerInventoryUI

This will be at least a 3 part series in which I will go over each aspect of my build which will cover the following topics:

  • Staging the SQL database and tables (This article)
  • Collecting server data and pushing to the SQL database (Part 2)
  • Pulling the data from the SQL database into a UI (Part 3)

This article will focus on the SQL database and table build using a few techniques. I consider this almost a living script as I have added and even removed a few things since its original inception. I opted to create tables for each section that will be inventoried. Currently, the sections are (in no particular order):

  • AdminShares – Holds the admin shares of a server
  • Drives – Holds the different drives on a server such as CD-ROMs and hard drives
  • General – Holds information that doesn’t really apply to the other tables
  • Groups – Holds local group information on the server
  • Memory – Holds the memory information of a server
  • Network – Holds networking configuration information of a server
  • OperatingSystem – Holds operating system information
  • Processor – Information about the processors on a server
  • ScheduledTasks – Lists all scheduled tasks on a server
  • ServerRoles – Lists all server roles, if applicable, on a server
  • Services – Lists all services on a server
  • Software – Lists all installed software on a server
  • Updates – Lists all installed updates on a server
  • Users – Lists all local users on a server
  • UserShares – Lists all user created shares on a server

Of course, this could change over time depending on various requirements, but I am hoping that for the most part, things stay the same and the only things that would need to be updated would be the content in each table such as adding or removing various properties. Adding new tables is as easy as copying the code from a different table creation region and making changes to add the new names and properties to include.

One last requirement that I had was that this data was only meant to last until the next run of the scheduled task which collects the data and sends it to SQL. This means that whatever that I had prior to the run will be replaced by the new data. Probably not the most efficient way to handle the data, but there is always room for improvement with this project.

The script that I wrote to help build not only the database, but the tables where I will push all of my data to is found at the following link: https://github.com/proxb/ServerInventoryReport/blob/master/Invoke-ServerInventorySQLBuild.ps1

I have defaulted the SQL server name to vSQL as that is my server in my lab environment so feel free to update it as needed or just call the script with the parameter –SQLServer and it will do the rest of the work for you! You can also see that I have my own version of Invoke-SQLCmd here. There are much better versions of this out in the wild so you can feel free to rip this one out and replace with a different one if you wish.

As for the column names and types, I tried to make it something that would ‘just work’ and also after some trial and error with figuring out a size for some of the data in the columns where I ran into errors when sending the data to SQL as something like a display name was longer than what was allowed in SQL. This might still be an issue for some of you depending on what you have running, but for that you can just the code and re-build the database.

Running the code is simple, just make sure that you point it to a valid SQL server and let it run!

.\Invoke-ServerInventorySQLBuild.ps1

image

It shouldn’t take too long to finish and after that, you can use SQL Server Management Studio (or whatever else you want to use) to validate that the database and tables have been created.

image

Part 2 will take us to performing the queries against remote server and sending the data collected up to the SQL server using a script that I wrote which will leverage my PoshRSJobs module for added multithreading.

Posted in powershell | Tagged , , , | Leave a comment

Speaking at Austin PowerShell User Group

image

This Thursday at 6pm CST I will be speaking at the Austin PowerShell User Group. I won’t actually be there (I wish I was there) but instead will be doing this remotely from my house. My topic will be on PowerShell runspaces which happens to be one of my favorite things to talk about.

This was supposed to have happened last month but I ended up being pretty sick and had to  reschedule. This week I am feeling great and looking forward to sharing my knowledge on PowerShell and runspaces!

Be sure to check out the link here for more information; https://www.meetup.com/Austin-PowerShell/events/237381901/

Posted in powershell | Tagged , , , , | Leave a comment

New Updates in PoshRSJob 1.7.3.5

I recently pushed out some new updates and bug fixes to my PoshRSJob module. I am continuing to provide updates to this module based not only on the feedback on the GitHub Issues page, but also on things that I happen to think about as well to continue to make this an amazing module to use!

This update only included 3 major updates that I focused on and each one was important to help ensure that the module is more stable and provides more accurate data. I’ll highlight each update and provide a little bit of information for each one.

image

This one was an odd bug that I hadn’t encountered while using the module and during testing. Fortunately for me, others have really put this module through the ringer and have found some bugs that I doubt that I would have ever found.

What was happening was that the runspacepool monitor was determining that a newly created runspacepool was already past its expiration timestamp and would then dispose of the runspacepool which resulted in errors when runspaces being built on the runspacepool was trying to be invoked. The solution to this was actually very simple: when the runspacepool is created, set the time of expiration to 5 minutes in the future to give Start-RSJob enough time to create and start the runspaces within that runspacepool. Once the runspaces are going, the runspacepool monitor will update the timestamp as long as runspaces are still being used within the pool.

image

This one is not exactly a major bug, but still one that had to be dealt with and squashed.

Instead of using Break to halt all activity in the Wait-RSJob command and any subsequent commands, I instead use return instead which allows other commands to run after it. I had originally use Break because I wanted to ensure that no other commands within Wait-RSJob would, causing potential errors later on in the command.

image

This one was an issue that was reported a long time after I built this module but in the back of my mind was something that I really wanted to solve. Being able to track each runspace as its own job and to know if it was truly running or waiting to run would really change how PoshRSJob would look. Prior to this, all jobs showed as Running regardless of how many jobs were running. This led to a lot of confusion as to whether jobs were actually being throttled (they were) and this just added fuel to my fire of solving this issue.

This led me down the path of finding a useful way of determining which runspaces in a runspacepool were actually running while others were still waiting in the queue to kick off. In the end, I took what I learned with that and applied it to PoshRSJob. You can see an example in the image below.

image

There is still plenty to do with this module and I hope to continue to implement the various feature requests that others have asked for as well as the things that I would like to see done to make this an amazing module. Of course, I am always looking for others to help out with this if they want to contribute to any of the posted issues or if they happen to have other ideas.

Posted in powershell | Tagged , | Leave a comment

2017 PowerShell Resolutions

I’m a little late this year in getting this blog posted. But I definitely wanted to get this out before the end of 2016. Smile

As with the last couple of years (2014, 2015), I wanted to look back on the previous years resolutions and see how I did as well as setting up my 2017 ones for everyone to check out.

So without further ado, let’s see what I had for last year and see if I managed to complete my resolutions!

  • Speak at a local conference (SQL Saturday, InfoTech, Nebraska.Code(), etc…). Yes I am using this again but really it is never guaranteed to speak at these things so making one would be great!

Well, I submitted for SQL Saturday as well as Nebraska.Code() but unfortunately didn’t get any of my talks accepted. I won’t call this one a bust since I did the tough part of preparing and submitting sessions. Anything that happens with the approval was out of my control.

  • Work with PowerShell Classes, etc…. These are new in PowerShell V5 and I honestly haven’t even messed with these much, so it is on my TODO list.

I did spend some times with classes in PowerShell V5 and even added some of them to my PoshRSJob module where appropriate as well as blogging about them on MCPMag.com. I’d call this one a success!

  • Start working on re-writing a module as a binary module in C# to better learn the language.

Unfortunately working with bug and feature requests on PoshRSJob took away from my attempts to convert a module from a script module to a binary module. Maybe next year!

  • Do something with web APIs and OAuth stuff. I keep meaning to invest some time with this but always get side tracked.

While I wanted to get to this and work more learning OAuth and working with web APIs, it just didn’t pan out for me. I was looking to use Yahoo Fantasy Football as a great means to do this but time wasn’t on my side.

Looking back at last year’s resolutions, I was probably 1.5 out of 4 (I’m counting my conference one as at least .5 for effort), not the best showing but that is the way that it goes. Maybe 2017 will be a little more better to me to knock some of my new ones out.

Speaking of 2017, my PowerShell resolutions for the coming year are ones that I hope to knock out during the year barring any sort of craziness which can and will happen. So here they are:

  • Speak at a user group or conference this year. I figure that I will lump these into one thing as I am sure that I will hopefully accomplish one of these in 2017.
  • Write at at least 2 blogs that relate to SQL or InfoSec. I recently took on a position as a SQL DBA after many years of working more in the infrastructure of Windows with Active Directory, DNS, etc…. At same time, I am also very interested in the InfoSec field so I feel that this would be a good way to get more into both topics using PowerShell.
  • Start a new PowerShell project. I have been working a lot on PoshRSJob to make it a great module and fix a ton of the bugs and feature requests that I have received for it this year, but I want to be able to put out a new project sometime this year. I have a few ideas but we will see which one will get pushed out.
  • Operational Validation with PowerShell and Pester. This is something that I have been starting to look at but plan on doing more in 2017 both in my job as well as with a blog or two.

What about you? I’d love to hear about what you are planning on doing in 2017 with PowerShell so be sure to leave them in the comments!

Happy New Year!

Posted in powershell | Tagged , , | 3 Comments

Tracking the State of PowerShell Runspaces in a Runspacepool

A question that I have received a number of times has always involved a way to determine the state of each runspace that is in a runspacepool. Since a runspacepool is used to throttle the number of runspaces that are able to run at a single time, it only makes sense that there should be a way to determine what runspaces are currently running and what ones are in a ‘NotStarting’ state. Knowing when a runspace is completed is simple with the IsCompleted property of the PowerShellAsyncResult object, but beyond that, it has been a mystery.

If you use my PoshRSJob module, you may have noticed that after you begin all of the RSJobs, that they all show a state of ‘Running’, which can create some confusion even though some of the jobs are not actually running just yet. Between this issue with my module and the number of questions that I had received, it has been one of my goals with runspaces to find a way to determine the state of each runspace in a runspacepool so others can make use of whatever technique that I could find as well as adding the discovered solution into my module.

The solution that I found begins with using reflection techniques to dig deeper into the PowerShell instance that we created and used with our runspacepool to find the state of a running pipeline and then looks at the IsCompleted property of the PowerShellAsyncResult object to determine whether that particular instance is in fact running or waiting to start in the runspacepool. in order to properly test this and ensure that I am not missing anything, I will have to have at least one runspace that has already been completed, one that is going to always be running and another one that should not be running yet.

To make this happen, I will kick off 6 runspaces within a runspacepool that will only allow a maximum of 2 concurrently running runspaces. The scriptblock for those will contain a While loop that will only break if the value being checked is an odd number. This means that at some point 2 runspaces will be completed,  2 will continue to run and another 2 will be waiting to start, but will never start due to the other jobs continuously running as they contain even numbers.

My runspacepool job code is below and uses the ideas that I described to ensure that I meet my goal of having runspaces in various states.

$RSP = [runspacefactory]::CreateRunspacePool(1,2)
$RSP.Open()

$List = New-Object System.Collections.ArrayList

$Result = 1..6 | ForEach {
    $PS = [powershell]::Create()
    $PS.RunspacePool = $RSP
    [void]$PS.AddScript({
        Param($i)
        While ($True) {
            Start-Sleep -Milliseconds 1000
            If ($i%2) {BREAK}
        }
    }).AddArgument($_)
    $List.Add(([pscustomobject]@{
        Id = $_
        PowerShell = $PS
        Handle = $PS.BeginInvoke()
    }))
}

 

You will notice that I saved the results of the BeginInvoke() command as well as the Id and PowerShell instance. This way we can accurately tell what values we are checking against as the Id matches the value of the If statement.

The contents of the list looks like this:

image

Based on my code in each runspace, we can easily deduce that Ids 1 and 3 should be completed while 2 and 4 should be running which leaves 5 and 6 as the remaining jobs left to run. a quick look at the Handle property of this object should show the IsCompleted property being True for 1 and 3 whereas the others will show as False.

image

This was the easy part. Knowing what has already finished isn’t what I set out to solve as it was already easy to find. I am now going to take a long look at Id 2 to look for a running pipeline which should give me an indication that there is something already running in the runspacepool.

In this example, we will look at Id 2 (which would be $list[1]) and will see if this is truly running like I am thinking it is.

The first thing I need to pull is the System.Management.Automation.PowerShell+Worker object from the PowerShell instance. This is not publicly available to view so we now begin our first dive into using reflection to get this object.

$Flag = 'static','nonpublic','instance'
$_Worker = $list[1].PowerShell.GetType().GetField('worker',$Flag)
$Worker = $_Worker.GetValue($list[1].PowerShell)

Using the proper flags to tell what kind of values we want for the Field, we can locate the nonpublic field and then take that object and pull the value using GetValue() and providing the original object that it belongs to, in this case the original object is the PowerShell instance. We can verify that we have the object and then move onto the next property to locate.

image

We are now halfway down our reflection rabbit hole. Taking this PowerShell+Worker object, we will go down one more level deeper and pull the CurrentlyRunningPipeline property out of this which will tell us if…you guessed it… if the runspace is currently running.

$_CRP = $worker.GetType().GetProperty('CurrentlyRunningPipeline',$Flag)
$CRP = $_CRP.GetValue($Worker)
$CRP

image

Perfect! If there is data in this property, then that means that there is something running in the runspace and that therefore means the runspace in the runspacepool is currently running!

Ok, just a single test doesn’t really tell us that we are on the right path. We should definitely run this against a runspace that we know has already completed, such as Id 1 to check that piece and then run this against an Id that should not be running yet, like Id 5. If both of those do not have anything within the CurrentlyRunningPipeline property, then we can make a reasonable guess that we have a working approach to tracking runspace activity.

First, let’s run this against the completed runspace (Id1: $List[0]) and see what happens.

$Flag = 'static','nonpublic','instance'
$_Worker = $list[0].PowerShell.GetType().GetField('worker',$Flag)
$Worker = $_Worker.GetValue($list[0].PowerShell)

$_CRP = $worker.GetType().GetProperty('CurrentlyRunningPipeline',$Flag)
$CRP = $_CRP.GetValue($Worker)
$CRP

image

No data in the property on the completed runspace. Now onto the runspace that we believe to be waiting to start (Id5: $list[4]).

$_Worker = $list[4].PowerShell.GetType().GetField('worker',$Flag)
$Worker = $_Worker.GetValue($list[4].PowerShell)

$_CRP = $worker.GetType().GetProperty('CurrentlyRunningPipeline',$Flag)
$CRP = $_CRP.GetValue($Worker)
$CRP

image

Another successful test! Based on the results of both the completed and presumed pending runspace, we can see that we have a good way to determine the state of the runspaces. The only thing left to do is make sure that we know the difference between a completed and pending runspace and all we have to do is look at the IsCompleted property that we talked about earlier in the article. When it shows True and has no data in CurrentlyRunningPipeline, then it is a completed runspace but if the IsCompleted is False with no data in the other property, then we know that this hasn’t started yet.

The final script for my demo shows what I did to create the runspaces and then to determine the state of the runspaces and returning a ‘Running’, ‘NotStarted’ and ‘Completed’ state for each runspace.

$RSP = [runspacefactory]::CreateRunspacePool(1,2)
$RSP.Open()

$List = New-Object System.Collections.ArrayList

$Result = 1..6 | ForEach {
    $PS = [powershell]::Create()
    $PS.RunspacePool = $RSP
    [void]$PS.AddScript({
        Param($i)
        While ($True) {
            Start-Sleep -Milliseconds 1000
            If ($i%2) {BREAK}
        }
    }).AddArgument($_)
    $List.Add(([pscustomobject]@{
        Id = $_
        PowerShell = $PS
        Handle = $PS.BeginInvoke()
    }))
}
Start-Sleep -Seconds 2 
$Flag = 'static','nonpublic','instance' 
0..5 | ForEach {
    $_Worker = $list[0].PowerShell.GetType().GetField('worker',$Flag)
    $Worker = $_Worker.GetValue($list[$_].PowerShell)

    $_CRP = $worker.GetType().GetProperty('CurrentlyRunningPipeline',$Flag)
    $CRP = $_CRP.GetValue($Worker)
    $State = If ($list[$_].handle.IsCompleted -AND -NOT [bool]$CRP) {
        'Completed'
    } 
    ElseIf (-NOT $list[$_].handle.IsCompleted -AND [bool]$CRP) {
        'Running'
    }
    ElseIf (-NOT $list[$_].handle.IsCompleted -AND -NOT [bool]$CRP) {
        'NotStarted'
    }
    [pscustomobject]@{
        Id = (([int]$_)+1)
        HandleComplete = $list[$_].handle.IsCompleted
        PipelineRunning = [bool]$CRP
        State = $State
    }
}

Running this code will produce the following output which will show the states for each runspace:

image

Note that I haven’t yet added this to PoshRSJob as I need to ensure that I add the appropriate code within the Type file within the module so the correct results are shown each time you run Get-RSJob. I will be sure to update this blog and post out to Twitter when that has occurred. Note that if you use this approach that you will either have to use this each time you want to view the status of the runspace, or you create a custom object with its own specific type and then supply your own type file (or use Update-TypeData) to automatically perform the checks each time you look at the object.

Posted in powershell | Tagged , , , | 3 Comments