Link to home
Start Free TrialLog in
Avatar of Ian_Clubb
Ian_Clubb

asked on

WMI hardware inventory

Hi
Below is a script that references WMI to create a spreadsheet. It checks the serial number of the PC and if it is found in the spreadsheet it overites the row, it it is a new entry it uses the next available row.
I run into problems when multiple users write to the file at once, even with the workbook shared it corrupts regularly.
I would like it to function in the same way with regards to the serial number check and what info it retrieves form WMI, but write it into a table of an access database not an excel file.
I have no scripting knowledge and this script was wrote by someone on this site.
Any help would be appreciated.
Thanks
Ian

'On Error Resume Next

' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScript.Network")

strComputer = "."
strWorkBook = "\\eastcotefps\assetscript$\asset.xls"

' Create Excel Spreadsheet
Set app = CreateObject("Excel.Application")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
ws.Cells(1,1).Value = "Computername"
ws.Columns(1).ColumnWidth = 20
ws.Cells(1,2).Value = "Username"
ws.Columns(2).ColumnWidth = 30
ws.Cells(1,3).Value = "Manufacturer"
ws.Columns(3).ColumnWidth = 20
ws.Cells(1,4).Value = "Model"
ws.Columns(4).ColumnWidth = 20
ws.Cells(1,5).Value = "Serial Number"
ws.Columns(5).ColumnWidth = 30
ws.Cells(1,6).Value = "CPU"
ws.Columns(6).ColumnWidth = 30
ws.Cells(1,7).Value = "CPU Speed"
ws.Columns(7).ColumnWidth = 10
ws.Cells(1,8).Value = "Operating System"
ws.Columns(8).ColumnWidth = 40
ws.Cells(1,9).Value = "Service Pack"
ws.Columns(9).ColumnWidth = 20
ws.Cells(1,10).Value = "Total Memory"
ws.Columns(10).ColumnWidth = 20
ws.Cells(1,11).Value = "Audit Date"
ws.Columns(11).ColumnWidth = 20
ws.Rows(1).Font.Bold = True

' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem In colItems
strSerialNumber = "" & objItem.SerialNumber
Next
Set objWMIService = Nothing
Set colItems = Nothing

intRowToUse = -1
For intRowCount = 2 To ws.UsedRange.Rows.Count
      If Trim(strSerialNumber) = Trim(ws.Cells(intRowCount,5).Value) Then
            intRowToUse = intRowCount
      End If
Next
If intRowToUse = -1 Then
      intRowToUse = ws.UsedRange.Rows.Count + 1
End If

' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,1).Value = "" & objItem.Caption
    ws.Cells(intRowToUse,2).Value = "" & objItem.UserName
    ws.Cells(intRowToUse,3).Value = "" & objItem.Manufacturer
    ws.Cells(intRowToUse,4).Value = "" & objItem.Model
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
ws.Cells(intRowToUse,5).Value = strSerialNumber

'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,6).Value = "" & objItem.Name
    ws.Cells(intRowToUse,7).Value = "" & objItem.CurrentClockSpeed
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objItem In colItems
    ws.Cells(intRowToUse,8).Value = "" & objItem.Caption
    ws.Cells(intRowToUse,9).Value = "" & objItem.CSDVersion
    ws.Cells(intRowToUse,10).Value = "" & FormatNumber(objItem.TotalVisibleMemorySize/1024,0)  
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
ws.Cells(intRowToUse,11).value = "" & Day(Now) & "-" & Month(Now) & "-" & Year(Now)

'Autofit all columns
app.ActiveSheet.Columns.EntireColumn.AutoFit
' Save Audit File
app.DisplayAlerts = False
'wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit

MsgBox "Done"
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

My suggestion is to use an Access table to be accessed by different users for the necessary updates first. Then, at the end of the day, use a similar routine as you have above to update your excel files in one shot.

Mike
Avatar of Ian_Clubb
Ian_Clubb

ASKER

I'm sorry i dont understand what your getting at, this is to perform a hardware inventory on all the machines on my network, i have it running as a scheduled task every hour. Currently it writes to an excel file, problem is it keeps corrupting. I want to do away with the excel file completely and have the data go straight into an access database.
The other problem is I have no scripting knowledge what-so-ever..........
<even with the workbook shared it corrupts regularly.>
That's what shared workbooks do best :-(

How many people are your talking about logging in at the same time?
up to 400 when I role it out company wide
Avatar of RobSampson
A database could be overkill.....what about an alternative.....
What I am thinking of is:
1) Have a dedicated network share for the list of files for the Hardware Inventory
2) On each hour, each PC writes it's inventory information to this share, but writes a flat text file with the computer name, or in your case, probably the serial number, as the file name.  That should overcome any multiple access problems.
3) A separate script that then be run manually, or maybe ten minutes after each hour ( to allow for slow machines) that will iterate through every file in that dedicated share, and pull them all together in an excel spreadsheet.  That way, only one process will be writing to the spreadsheet, which would probably be a scheduled task on that server that houses the share.

If that sounds like a good option for you, I can work on that script.

Regards,

Rob.
Sounds good, thanks very much
OK, so here's the script to write the same info to a text file with a name of the serial number.  Just change the UNC path to match that in your environment.  I will work on the combining of the scripts into a spreadsheet tomorrow.  I'm going home now.
'==============
Option Explicit

'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile

Set objFSO = CreateObject("Scripting.FileSystemObject")

' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScript.Network")
strComputer = "."

' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem In colItems
      strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing

strFileName = "\\NTFP\Rsampson\Scripting\Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"

' Create Excel Spreadsheet
strResults = "Computername;Username;Manufacturer;Model;Serial Number;CPU;CPU Speed;Operating System;Service Pack;Total Memory;Audit Date" & VbCrLf

' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems
      strResults = strResults & Trim(objItem.Caption)  & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer) & ";" & Trim(objItem.Model)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber

'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem In colItems
      strResults = strResults & ";" & Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockSpeed)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objItem In colItems
    strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem.TotalVisibleMemorySize/1024,0))
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))

Set objOutputFile = objFSO.CreateTextFile(strFileName, True)
objOutputFile.Write strResults
objOutputFile.Close

Set objOutputFile = Nothing
Set objFSO = Nothing

MsgBox "Done"
'==================
The UNC path for you to change is:
strFileName = "\\NTFP\Rsampson\Scripting\Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"

Regards,

Rob.
By the way, if anyone is interested, I use almost exactly the same method (but with much less information) to write each user's User login name, IP Address, Computername, and date and time to a UNC text file that is called their login name, appending each instance of a login to the top of the file.  Then with these files I know exactly where this person logged on and when.

Regards,

Rob.
Whoops, I've just noticed that it outputs the CPU details twice....must be the dual core processor in my system.  Use this full code version instead:
'===========================
Option Explicit

'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile, strCPUDetails

Set objFSO = CreateObject("Scripting.FileSystemObject")

' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScript.Network")
strComputer = "."

' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem In colItems
      strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing

strFileName = "\\NTFP\Rsampson\Scripting\Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"

' Create Excel Spreadsheet
strResults = "Computername;Username;Manufacturer;Model;Serial Number;CPU;CPU Speed;Operating System;Service Pack;Total Memory;Audit Date" & VbCrLf

' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems
      strResults = strResults & Trim(objItem.Caption)  & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer) & ";" & Trim(objItem.Model)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber

'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem In colItems
      strCPUDetails = Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockSpeed)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strResults = strResults & ";" & strCPUDetails
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objItem In colItems
    strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem.TotalVisibleMemorySize/1024,0))
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))

Set objOutputFile = objFSO.CreateTextFile(strFileName, True)
objOutputFile.Write strResults
objOutputFile.Close

Set objOutputFile = Nothing
Set objFSO = Nothing

MsgBox "Done"
'=====================

Regards,

Rob.
Thanks Rob that works well, can you ensure that when the text files are dumped into the spreadsheet that the serial number is checked and that it is either overitten or a new row is created.
Thanks for your help
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Rob
When I run the script to create the excel file excel opens and it changes the font to bold but then I get a runtime error, line 37, char 13, error "file not found" code 800A0035.
I've checked and double checked the paths I've put in, hope i'm not doing something stupid!!
Thanks
Ian
Hmmm, just under this line:
For Each objFile In objFSO.GetFolder(strInventoryFolder).Files
add these two lines
MsgBox "Looking in " & strInventoryFolder & " at " & vbCrLf & _
"file: " & objFile.Name

and you should see a message box appear for each file (maybe make sure there's only five or so files) that should tell you what folder it's looking in, and each filename that it will try to open on the next line.

I changed strInventoryFolder to a dot ".", just because I put the script itself in the same folder as these text files, so the dot just tells it to look int the current folder.  If you don't have the script in the same folder, make sure you put in the full path (by UNC should work) to the text files.

Regards,

Rob.
yep, put the scrip tin the folder with the text files and it worked.
Thanks very much for all your help
No problem.  As I mentioned, you can put the script anywhere, just change the strWorkBook and strInventoryFolder values to suit.

Regards,

Rob.