Link to home
Start Free TrialLog in
Avatar of rtmcmullen
rtmcmullen

asked on

WMI output to "Append" to Excel?

I'm stuck on this one, I have an asset inventory script using vbscript that pulls WMI info and saves it into an excel spreadsheet.  What I am trying to do are two things.  1) I'm trying to save to a location other that the root of C:, like a UNC path preferebly.  and 2) trying to append to the excel doc that it save in the next column down when it is run in succession.  So that for a week it will have 5 columns written to..

Here is what I have so far:


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 = InputBox ("Enter Computername")
strComputer = "."

' Create Excel Spreadsheet
Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Add
app.Visible = False
app.AlertBeforeOverwriting = True
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 = "BIOS Version"
ws.Columns(8).ColumnWidth = 20
ws.Cells(1,9).Value = "BIOS Install Date"
ws.Columns(9).ColumnWidth = 30
ws.Cells(1,10).Value = "Timezone"
ws.Columns(10).ColumnWidth = 30
ws.Cells(1,11).Value = "Operating System"
ws.Columns(11).ColumnWidth = 40
ws.Cells(1,12).Value = "Service Pack"
ws.Columns(12).ColumnWidth = 20
ws.Cells(1,13).Value = "Windows Directory"
ws.Columns(13).ColumnWidth = 20
ws.Cells(1,14).Value = "Total Memory"
ws.Columns(14).ColumnWidth = 20
ws.Cells(1,15).Value = "C:\File System"
ws.Columns(15).ColumnWidth = 20
ws.Cells(1,16).Value = "C:\Disk Space"
ws.Columns(16).ColumnWidth = 20
ws.Cells(1,17).Value = "C:\Free Space"
ws.Columns(17).ColumnWidth = 20
ws.Cells(1,18).Value = "D:\File System"
ws.Columns(18).ColumnWidth = 20
ws.Cells(1,19).Value = "D:\Disk Space"
ws.Columns(19).ColumnWidth = 20
ws.Cells(1,20).Value = "D:\Free Space"
ws.Columns(20).ColumnWidth = 20
ws.Cells(1,21).Value = "CDROM Drive Letter"
ws.Columns(21).ColumnWidth = 20
ws.Cells(1,22).Value = "Network Adapter"
ws.Columns(22).ColumnWidth = 30
ws.Cells(1,23).Value = "MAC Address"
ws.Columns(23).ColumnWidth = 20
ws.Cells(1,24).Value = "Swap File Size"
ws.Columns(24).ColumnWidth = 20
ws.Cells(1,25).Value = "Monitor Manufacturer"
ws.Columns(25).ColumnWidth = 20
ws.Cells(1,26).Value = "Monitor Type"
ws.Columns(26).ColumnWidth = 20
ws.Cells(1,27).Value = "Monitor Description"
ws.Columns(27).ColumnWidth = 20
ws.Cells(1,28).Value = "Display Mode"
ws.Columns(28).ColumnWidth = 30
ws.Cells(1,29).Value = "Video Card Description"
ws.Columns(29).ColumnWidth = 50
ws.Cells(1,30).Value = "Video Card RAM"
ws.Columns(30).ColumnWidth = 20
ws.Cells(1,31).Value = "Location"
ws.Columns(31).ColumnWidth = 20
ws.Cells(1,32).Value = "Audit Date"
ws.Columns(32).ColumnWidth = 20
ws.Cells(1,33).Value = "Removable Drive Type"
ws.Columns(33).ColumnWidth = 30
ws.Cells(1,34).Value = "Computer Role"
ws.Columns(34).ColumnWidth = 20
ws.Cells(1,35).Value = "IP Address"
ws.Columns(35).ColumnWidth = 20
'
' 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(2,1).Value = "" & objItem.Caption
    ws.Cells(2,2).Value = "" & objItem.UserName
    ws.Cells(2,3).Value = "" & objItem.Manufacturer
    ws.Cells(2,4).Value = "" & objItem.Model
Next
Set objWMIService = Null
Set colItems = Null
'
'Get BIOS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem In colItems
ws.Cells(2,5).Value = "" & objItem.SerialNumber
ws.Cells(2,8).Value = "" & objItem.Version
    ws.Cells(2,9).Value = "" & objItem.InstallDate
Next
Set objWMIService = Null
Set colItems = Null
'
' 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(2,6).Value = "" & objItem.Name
    ws.Cells(2,7).Value = "" & objItem.CurrentClockSpeed
Next
Set objWMIService = Null
Set colItems = Null
'
'
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_TimeZone",,48)
For Each objItem In colItems
    ws.Cells(2,10).Value = "" & objItem.StandardName
Next
Set objWMIService = Null
Set colItems = Null
'
' 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(2,11).Value = "" & objItem.Caption
    ws.Cells(2,12).Value = "" & objItem.CSDVersion
    ws.Cells(2,13).Value = "" & objItem.WindowsDirectory
    ws.Cells(2,14).Value = "" & FormatNumber(objItem.TotalVisibleMemorySize/1024,0)    
Next
Set objWMIService = Null
Set colItems = Null
'
' Get C:\ Drive Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk where DeviceID = 'C:' and DriveType = '3'",,48)
For Each objItem In colItems
    ws.Cells(2,18).Value = "" & objItem.FileSystem
    ws.Cells(2,19).Value = "" & FormatNumber(objItem.Size/2^30,1)
    ws.Cells(2,20).Value = "" & FormatNumber(objItem.FreeSpace/2^30,1)
Next
'
'Get D:\ Drive Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk where DeviceID = 'D:' and DriveType = '3'",,48)
For Each objItem In colItems
    ws.Cells(2,18).Value = "" & objItem.FileSystem
    ws.Cells(2,19).Value = "" & FormatNumber(objItem.Size/2^30,1)
    ws.Cells(2,20).Value = "" & FormatNumber(objItem.FreeSpace/2^30,1)
Next
Set objWMIService = Null
Set colItems = Null
'
' Get CDROM Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_CDROMDrive",,48)
For Each objItem In colItems
    ws.Cells(2,21).Value = "" & objItem.Drive    
Next
Set objWMIService = Null
Set colItems = Null
'
' Get Network Card Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE",,48)
For Each objItem In colItems
    ws.Cells(2,22).Value = "" & objItem.ServiceName(0)
    ws.Cells(2,23).Value = "" & objItem.MACAddress(0)
Next
'
' Get Paging File Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PageFile",,48)
For Each objItem In colItems
    ws.Cells(2,24).Value = "" & FormatNumber((objItem.FileSize/1024)/1024)    
Next
Set objWMIService = Null
Set colItems = Null
'
' Get Monitor Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_DesktopMonitor",,48)
For Each objItem In colItems
    ws.Cells(2,25).Value = "" & objItem.MonitorManufacturer
    ws.Cells(2,26).Value = "" & objItem.MonitorType
    ws.Cells(2,26).Value = "" & objItem.DescriptiOn
Next
Set objWMIService = Null
Set colItems = Null
'
' Get Video Card Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_VideoController",,48)
For Each objItem In colItems
    ws.Cells(2,28).Value = "" & objItem.VideoModeDescription
    ws.Cells(2,29).Value = "" & objItem.Description
    ws.Cells(2,30).Value = "" & FormatNumber((objItem.AdapterRAM/1024)/1024)
Next
Set objWMIService = Null
Set colItems = Null
'
' Get & Writeout current Date
ws.Cells(2,32).value = "" & Day(Now) & "-" & Month(Now) & "-" & Year(Now)
'
' Get IP Address
Set objWMIService = GetObject _
    ("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration " & _
        "Where IPEnabled = True")
'For Each objItem in colItems
'      ws.Cells(2,35).Value = "" & objAddress 'objItem.IPAddress
For Each objAddress in objItem.IPAddress
        ws.Cells(2,35).Value = "" & objAddress
Next
Set objWMIService = Null
Set colItems = Null

' Save Audit File
app.AlertBeforeOverwriting = False
ws.SaveAs "c:\" & WshNetwork.ComputerName & "_" & WshNetwork.UserName & "-pcaudit.xls"
app.quit
Avatar of Jose Parrot
Jose Parrot
Flag of Brazil image
Hi,

As you are already using several collumns, it will be quite complicated to create a sheet with 35 x 7 collumns. Seems to be reasonable to create one sheet for each week day, so you may use the same code to fill them each day, changing only the active sheet as per the week day.

About save to another path than c:\ , I don't understand which is the problem. Also what criteria do you want to declare the path?

Jose
Avatar of rtmcmullen
rtmcmullen

ASKER

I figured it would be quite difficult to achieve have added columns, not a big deal.  For the save path, I want to run for login scripts and have this save to a UNC path.  The current will only save to the root of C: at the end of the script.
Avatar of rtmcmullen
rtmcmullen

ASKER

Just to note, the one I'm working from is using a lot of "columns", but I wanted to append "rows" to the colums in succession.  Purpose is to veiw the spreadsheet with multiple entries for each day.  Still quite complicated..  Thank : )
Avatar of Jose Parrot
Jose Parrot
Flag of Brazil image
Lets confirm my understanding.

You have created a table with

                    Column 1        Column 2        ...       Column 35
Row 1:    Computer Name   User Name                 IP Address
Row 2:    jsmith                 John_Smith      ...       192.168.21.123    

As per your code, when the script runs, data will be overwriten ever in row 2.
What you want is:
           
                    Column 1        Column 2        ...       Column 35
Row 1:    Computer Name   User Name                 IP Address
Row 2:    jsmith                 John.Smith      ...       192.168.21.123    <-- Day 1
Row 3:    jsmith                 John.Smith      ...       192.168.21.101    <-- Day 2
Row 4:    jsmith                 John.Smith      ...       192.168.21.153    <-- Day 3
Row 5:    jsmith                 John.Smith      ...       192.168.21.92      <-- Day 4
Row 6:    jsmith                 John.Smith      ...       192.168.21.132    <-- Day 5

saved in the "jsmith_John.Smith-pcaudit.xls" file.
To do that, an auxiliary counter is stored in a free cell, say (1,36).
When the script is executed at first time, as soon it opens the Excel file, it reads the content of such cell, before write to any cell:

   Dim count As Integer
   if ws.Cells(1,36).Value = ""
   then
         count = 2
   else
         count =  ws.Cells(1,36).Value + 1
   endif
   ws.Cells(1,36).Value = count
   
The variable "count" will be used in your code, in place of each row direct number. For example, the code

' 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(2,1).Value = "" & objItem.Caption
    ws.Cells(2,2).Value = "" & objItem.UserName
    ws.Cells(2,3).Value = "" & objItem.Manufacturer
    ws.Cells(2,4).Value = "" & objItem.Model
Next

will be substituted by

' 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(count,1).Value = "" & objItem.Caption
    ws.Cells(count,2).Value = "" & objItem.UserName
    ws.Cells(count,3).Value = "" & objItem.Manufacturer
    ws.Cells(count,4).Value = "" & objItem.Model
Next

and so on.

Please note, if another person, say Paul Adams use jsmith pc, then a new file will be created: "jsmith_Paul.Adams-pcaudit.xls"

About the path, to access a server at the network without mapping a Drive it is enough to use  the sintax "\\server\volume\folder" in place of "C:\".
Assuming the server as "fileserver1" and the folder as "auditfolder" substitute

ws.SaveAs "c:\" & WshNetwork.ComputerName & "_" & WshNetwork.UserName & "-pcaudit.xls"

by

MyFilePath = "\\" & "fileserver1" & "\" & "auditfolder" & "\"
ws.Save MyFilePath & WshNetwork.ComputerName & "_" & WshNetwork.UserName & "-pcaudit.xls"

The same pathfile should be used in the beggining of your script, to open the spreadsheet:
ws.Open MyFilePath & WshNetwork.ComputerName & "_" & WshNetwork.UserName & "-pcaudit.xls"

So, the modifications are:

1. Open the xls file
2. Determine new value for count
3. Take data from the PC
4. Store new rows with data in the sheet
5. Save the xls file

Jose

 
Avatar of rtmcmullen
rtmcmullen

ASKER

Jose, thanks for the comprehensive response.  I did create the MyFilPath variable and was able to save to a UNC.  For adding additional rows though, it is overwriting each time and will not append the information, am I missing something?  Thanks again
ASKER CERTIFIED SOLUTION
Avatar of Jose Parrot
Jose Parrot
Flag of Brazil 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