PowerShell Server Inventory, Part 2: Collecting and Sending Data

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:

Picking up where I left at, we have created our database and tables that will be used to store all of our data on our servers. Next up is to begin querying each server in the domain and pull as much information as we can and then send that data up to SQL so we can view it later on.

For this part of querying and sending data to the SQL server, I felt that it would be best to do some multithreading to speed up the time that it would take to hit all of the systems rather than perform the work sequentially. My multithreading approach of choice is to use my module, PoshRSJob to handle this. You can grab this module from my GitHub repo and place it on whichever server that you will be using to schedule the task from to perform the scans and uploads to SQL.

I say that you can scheduled a job to run the next script, but this is something that could be performed manually as well if need be and in fact, I would recommend this to ensure that no errors are thrown during the script execution.

The script that I wrote which will be used is called Invoke-ServerInventoryDataGathering.ps1 which only has a single parameter that accepts what SQL server will be used to send the data to. The script itself is over 1000 lines of which most of it consists of various regions which are used to group each type of data pull ranging from user account information to drive data to system configurations. In fact, about half of the code consists of helper functions which are used for data gathering or, in the case of Get-Server, is used to perform a domain wide lookup of all of the servers for a more dynamic approach of ensuring that all servers are being checked rather than using a file to host all of the systems.

image

There is a variable called $ServerGroup which is keeping a value of ‘MemberServer’. You can disregard this (keep it uncommented though) as I have my lab set up differently where Domain Admins do not have access to anything other than domain controllers which require a second script to be used to query the domain controllers and then write the data to the SQL server (which does allow domain admins to access for the purpose of writing the data to SQL).

Continuing to step through the code,  I have several regions which I use to group together each type of query. If more queries need to be added to grab more information, I can simply create a new region and add the necessary code to gather and send the data.

image

Each region follows the same type of process:

  1. Query the remote system for the particular information that I need
  2. If the query was successful, then proceed to perform a check against the database for data on the same system on the table that matches the type of data being queried for
  3. If data already exists, then remove the existing data
  4. Send the new data up to the appropriate table based on the current data being collected

Kicking off the script manually is just a matter of running it. In my case, VSQL is hard coded as the parameter value for the SQL server. In other cases, you would want to supply your own value.

image

I use Wait-RSJob with the –ShowProgress switch so I can track the status of each job. Each job represents a computer that is being scanned and the data gathered sent to a SQL database for later viewing.

image

Of course,  the preferred use of this script is by putting it into a scheduled task so it can be run on a daily schedule to handle all of the updates or new systems that come into the environment.

image

With all of this done, we can quickly verify that data actually exists. We can either look in SQL Server Management Studio for the data, or run a simple PowerShell command using the Invoke-SQL function against one of the tables.

image

PowerShell Example

$TSQL = @"
SELECT TOP 1000 [ComputerName]
      ,[Manufacturer]
      ,[Model]
      ,[SystemType]
      ,[SerialNumber]
      ,[ChassisType]
      ,[Description]
      ,[BIOSManufacturer]
      ,[BIOSName]
      ,[BIOSSerialNumber]
      ,[BIOSVersion]
      ,[InventoryDate]
  FROM [ServerInventory].[dbo].[tbGeneral]
"@

Invoke-SQLCmd -Computername VSQL -TSQL $TSQL -Database ServerInventory -CommandType Query

image

All of this code as well as the rest of the Server Inventory code is available at my GitHub repo here: https://github.com/proxb/ServerInventoryReport

The last part in this series will take us through building a tool that will pull the data from SQL and provide users with a graphical way to view and generate reports.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell and tagged , , , , . Bookmark the permalink.

5 Responses to PowerShell Server Inventory, Part 2: Collecting and Sending Data

  1. Monterey Harris says:

    Very nice work. I have been working on developing a similar solution. Will your UI be web based or client based? Have tried expanding your inventory to include IPs?

    • Boe Prox says:

      My UI will be client based using WPF. I am pretty sure that IPs are already included under the Network tab. If you have anything that you think would be useful to collect, I’d love to hear it!

  2. powershell_md says:

    Nice iniciative. Didn’t see the code yet, but the idea is very nice. If i am not wrong the same idea is sustained by @cl Chrissy LeMaire.

  3. Pingback: PowerShell Server Inventory, Part 1: SQL Database Build | Learn Powershell | Achieve More

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s