Powershell - Get Inventory Script to Database
Background
The processes described on this page are an extenion of my original work using
Powershell to conduct WMI queries and then write the results off to an HTML file. The HTML process has served
us well at work for some years now, but I have occassionally thought that it would be great to also be able to
write some or all of the data back to a MySQL database. Recently I played around with this idea
and managed to achieve what I thought was a reasonable result. I have not attempted to load a database with
all the fields that the HTML file records, as for practical use this is meaningless. What I decided was to
keep the number of tables and fields down to what I feel would produce a meaningful and useful database and that if we
wanted more data about a single machine we could still review the HTML file.
Development
Firstly I had to work out how to interact with a MySQL database. As it turns out this is in fact not all that hard.
There are a number of useful web pages out there that can assist you in doing this. Once I had something working,
using a MySQL 5.0 database on an Ubuntu 9.04 server, I realised that there were at least two main ways of achieving this.
One was to use the ODBC MySQL driver to provide the means to connect to the database and the other way was to employ 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 provided can be installed
using the installer the reality for distribution purposes was that 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!
Database Setup
The script writes output to a MySQL server located on another machine. The MySQL server database needs to be set up as
indicated in this SQL file.
In my script a user called "inventory" with a password "inventory" must have the following permissions
on the ws_inventory database in MySQL:
SELECT
INSERT
UPDATE
DELETE
Of course if you change the username and password to something else you will need to adjust the script accordingly.
Script
Get the GetInventory_DB.ps1 Powershell script. Copy this script to the desired
location on the machine(s) you wish to get inventory data from. Also you will need to either install the
MySQL .NET connector or just copy in the
.NET assembly .DLL file called "MySql.Data.dll". 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...
Prerequisites
These are the same as listed in the HTML version of this process.
Issues
These are the same as listed in the HTML version of this process.
Operationally
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.
|
