CITRIXEN: PowerShell Server Inventory, Part 2: Collecting and Sending Data

PowerShell Server Inventory, Part 2: Collecting and Sending Data

http://ift.tt/2nMjgT8

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 (Part 1)
Collecting server data and pushing to the SQL database (Part 2) (This article)
Pulling the data from the SQL database into a UI (Part 3)

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.

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.

Each region follows the same type of process:

Query the remote system for the particular information that I need
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
If data already exists, then remove the existing data
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.

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.

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.

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.

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

All of this code as well as the rest of the Server Inventory code is available at my GitHub repo here: http://ift.tt/2l6O03a

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.

Filed under: powershell Tagged: inventory, Powershell, server, SQL, tsql