I have been playing around with WinAudit for the past week.  Overall it is an awesome little package.  One of it’s major selling points is the fact you can stuff the audit results into a database.  What more could an admin want?

The only drawback is the schema of the collected data.  In order to be be “futureproof” the data is stored into a single table.  The data is stored with a “Category” and “ItemName” with up to 5 values.  For example, the version of a software package would have a Category of “Installed Software”, an ItemName of “Adobe Flash” and an ItemValue1 of “10.0.45.2”.  The type of the item values is implied and not really specified anywhere.

This is great for extensibility since you can add new lines with reckless abandon.  Need to add some data about the CPU  temperature, no problem!  Simply add a “Category” of Hardware, an “ItemName” of CPU Temp and set the value to the temperature.  The problem is that it is pants for generating reports or summary type operations.

What you want is another table that contains the data as columns.  The picture to the left shows what I mean.

This problem is not insurmountable.  We could take the long, hard road and select the values one by one based on the Category and ItemName, inserting them into another table with columns based on the selected data point.  This is a pain and fraught with danger.  There has to be a better way.

The better way is using the Pivot command in SQL.  As shown below, it is all done in one command.  Unfortunately it isn’t crystal clear what is going on.  Let’s take a look at some key parts…

  1: INSERT INTO [IT].[dbo].[ComputerInfo]
  2:            ([ComputerName]  ,[DomainName]
  3:            ,[Description]   ,[OS]
  4:            ,[Manufacturer]  ,[Model]
  5:            ,[SerialNumber]  ,[AssetTag]
  6:            ,[Processors]    ,[ProcessorDescription]
  7:            ,[Memory]        ,[HardDrive]
  8:            ,[BIOS]          ,[UpTime]
  9:            ,[Roles])
 10: Select max([Computer Name]) As ComputerName,
 11:        max([Domain Name]) as DomainName, max([Description]) as Description,
 12:        max([Operating System]) as OperatingSystem, 
 13:        max([Manufacturer]) as Manufacturer, max([Model]) as Model,
 14:        max([Serial Number]) as SerialNumber, max([Asset Tag]) as AssetTag,
 15:        max([Number Of Processors]) as NumProcs, 
 16:        max([Processor Description]) as ProcDescription,
 17:        max([Total Memory]) as TotalMemory,
 18:        max([Total Hard Drive]) as TotalHDD, 
 19:        max([BIOS Version]) as BIOS, max([System Uptime]),
 20:        max([Roles]) As Roles
 21:   From [IT].[dbo].[WinAudi2] as data
 22: pivot (max(ItemValue1) for ItemName in 
 23:       ([Computer Name], [Domain Name], [Description],
 24:        [Roles], [Operating System], [Manufacturer], [Model],
 25:        [Serial Number], [Asset Tag], [Number Of Processors],
 26:        [Processor Description], [Total Memory], [Total Hard Drive], [System Uptime], [BIOS Version]
 27:       )) as result
 28:    where Category = 'System Overview'
 29:    group by Computer

This is an overly complex example as I needed to combine several different audit records for the same PC.  The basic structure of the pivot command is as follows…

  1: Select <New Columns> 
  2:   From <Original Table> as TableTag
  3:  Pivot (AggrergateCommand(Column to use as value) 
  4:            for OtherColumn in
  5:              ([Value that OtherColumn is equal to],
  6:               [Next value that OtherColumn is equal to],
  7:               ...)
  8: 

Pivoting is done on a single column.  In our case, it is the ItemName column.  When ItemName is “Computer Name” we want the value in the ItemValue1 column to be the value associated with the ComputerName column.  Makes sense.

I was confuse by the [Name] notation at first.  This is basically a faux variable that is also used as a comparison value.  Basically the internal phrase is used as ItemName = ‘Operating System’ for example. 

The problem is that many rows might have ItemName = ‘Operating System’.  That is why we have to have some sort of aggregation function.  I chose Max() since it is simple and the values should be equal anyway.

I am the IT department for a small company.  The lead developer, system admin, DBA and desktop support.  The whole enchilada.  As you can imagine, my time is limited.

One of the major aspects of my job is to keep track of the various assets under my control.  This involves both hardware and software.  With shuffling machines from place to place, people changing jobs and the addition (or removal) of stuff makes this a moving target.

RunAs Radio recently had an episode (#150 with Doug Toombs) that brought some new tools to my attention.  The ones of interest are WinAudit and KeyFinder. 

WinAudit takes a quick look at the computer hardware and software.  It then dumps the data into a various number of formats from CSV, to PDF to actually stashing it in a database which is pretty cool.  The thing that makes this application smoking hot is that it can be run via the command line!

KeyFinder is a little less cool in that it doesn’t store it’s information to the database.  It does, however, dig deep to find the keys used to install the various software packages.  A definite boon in my line of work.

In future posts I will go through what steps I went through to install these packages in our production environment.  Hopefully the end result will be a database that can be used to keep track of our software compliance and the state of our aging fleet of machines.