Automate Equipment Inventory to Database

It's been a while since I have posted to this blog. Mostly it has been because we had some major staff movements that have taken some time to work through to the point where making some space to write here is now somewhat realistic!

I want to share an improvement to a script I blogged in Feb 2007 which was about using a Windows Powershell script to query the WMI Win32_Product so as to retrieve information about all the software on a computer.

The original script output to a HTML file and has served us well, but I have occasionally thought that it would be great to also be able to write some or all of the data to a MySQL database. Recently I played around with this idea and managed to achieve a reasonable result.

I decided to use the .NET assembly available for this purpose from the MySQL site. The reason I decided to use the .NET assembly was that although the assembly can be installed using the installer the reality for distribution purposes was the the installer in fact copies in and then registers a .DLL file which if you want, can just be directly copied onto the target systems. Providing you specify the exact location of this file in the Powershell script, the fact that it is not "registered" does not matter!

Setup

1. As the script writes output to a MySQL server located on another machine. You can get the setup for the MySQL server from my personal website.
2. You can get a copy of the GetInventory_DB.ps1 Powershell script and copy this script to the desired location on the machine(s) you wish to get inventory data from.
3. Also you will need to either install the MySQL .NET connector or download it and just copy in the .NET assembly .DLL file called "MySql.Data.dll" to the desired location on the machine(s) you wish to get inventory data from.

Note: If you use different file locations for these files than what the script uses, you will need to edit the script accordingly. Also you will need to edit the location of the MySQL server...

We schedule the script to run from the AT command once a day on the local machines when the workstations are not in use. This means that the data is up to date with any changes made in the previous 24 hours. Intially we are accessing the MySQL database using an ODBC connector and a MS Access Database. I have since written a PHP script that interacts with the MySQL database. Rename the script "search.php" and edit it in the places commented so as to reflect your database, username and password and it should work! (This script was copied directly from my test server...).

PERL Data File Search Script

In the Powershell - Get Inventory Script process I posted last month I briefly mentioned a web based server script that we use to search the data output files we copy to our intranet server. Because I have noticed a significant boost in traffic to the website hosting the Get Inventory Script, I have cleaned up and rewritten the PERL script I developed for searching these files and I am making it available here. I used PERL for this task as the process runs for us, on a Linux intranet server, which of course has PERL natively installed.

This PERL script is designed to locate all the HTML files in a single directory. These files are in fact named for the hostname of the workstations that originally generated them when they were created by the Powershell - Get Inventory Script, so the script either searches for computer names and displays the complete data sets for each computer on one page, or it searches each file to locate the data string requested and displays the results. Which type of search is in fact done, is dependant on the users choice in the original search (Hostname or Keyword). If a Keyword search is selected any HTML tags in the source code are removed, before comparing the remaining text data with the string being sort. This reduces the number of false hits!

By searching all the data files harvested (copied) from the workstations there is no need to set up a database to store the data and the interaction overhead this causes. Because we are seaching ALL the files content we are able to effectively isolate discrete data quickly. For example: If I am asked for the number of installations of EndNote we have. I can set up a Keyword search for EndNote the returning data will advise me of the number of workstations with Endnote (124 as of writing) and then list for me the names of the workstations followed by the version details of EndNote on each workstation.

Links:

Download and implement the PERL Data File Search Script.

View the Powershell - Get Inventory Script post.

Automated Equipment Inventory

Our team supports some 300 workstations and one of the most tiresome tasks for support staff is to record accurately the inventory details of all the workstations in use. Because we are keen for the equipment to do as much routine work as possible, we use a Windows Powershell script to query the WMI Win32_Product which is designed to retrieve information about all the software on a computer that was installed using the Windows Installer (e.g., a .MSI file). The Win32_Product is not foolproof - it can miss applications that really were installed using the Windows Installer - but it seems to have no problem identifying most major software programs. We also capture patches and hardware details in the output.

The script outputs the data it retrieves to a HTML file located on the C:\ drive of the workstation that it is run on. We leave a copy of the output file on the C:\ drive so we can open it if we navigate to the C$ share over the network. We also copy the output file onto a server which allows us to browse or search the files using another script.... Typically the output will look like the output from my personal computer. I really like the way this script gets details like the processor ID number, the MAC address and the serial numbers off the BIOS. It makes the output file almost like a "finger print" of the machine. (Which is great news if a laptop goes missing and the police need the details AND you have a copy of the file on a server!)

The script is available together with more details about it here. We used Windows Powershell to solve this problem because it is a new product (at the start of it's life cycle) and is compatible with the current and upcoming Microsoft operating system releases.

If you are using Windows XP you will need to load Microsoft Windows Powershell, on the workstations on which you want to run the script. You will also need to set the Powershell script execution policy to: "set-executionpolicy remotesigned". The prerequisites for Powershell on XP are Service Pack 2 and the Microsoft .NET Framework version 2.0

12-06-2007 Added a new section that identifies all the usernames that have a profile on the workstation....
14/07/2007 Inserted new section to display Norton/Symantec AntiVirus Status if the application is present...