Solved

WMI output to "Append" to Excel?

Posted on 2006-10-31
6
3,096 Views
Last Modified: 2009-04-05
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
0
Comment
Question by:rtmcmullen
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:JoseParrot
Comment Utility
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
0
 

Author Comment

by:rtmcmullen
Comment Utility
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.
0
 

Author Comment

by:rtmcmullen
Comment Utility
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 : )
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:JoseParrot
Comment Utility
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

 
0
 

Author Comment

by:rtmcmullen
Comment Utility
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
0
 
LVL 18

Accepted Solution

by:
JoseParrot earned 500 total points
Comment Utility
Actually the suggestions I made result in overwrite the entire file, but adding a new row each time the script is invoked.

Lets follw the steps:

1. Open the xls file
    MyFilePath = "\\" & "fileserver1" & "\" & "auditfolder" & "\"
    ws.Open MyFilePath & WshNetwork.ComputerName & "_" & _
        WshNetwork.UserName & "-pcaudit.xls"
    (if not found, simply create a new spreadsheet)

2. Determine new value for count
   Dim count As Integer
   if ws.Cells(1,36).Value = ""        ' if it is the first time we use the program
   then
         count = 2                                    
   else                                          ' if the spreadsheet already exists
         count =  ws.Cells(1,36).Value + 1
   endif
   ws.Cells(1,36).Value = count

   For exemple, new count value is 3, so the row 2 is already filled and the sheet is
                        Column 1        Column 2        ...       Column 35
    Row 1:    Computer Name   User Name                 IP Address
    Row 2:    jsmith                 John_Smith      ...       192.168.21.123

3. Take data from the PC
    This is original code modified to use variable count intead of number 2:
    ' 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

4. Store new rows with data in the sheet
    So data is writen in row 3, then sheet now is
                        Column 1        Column 2        ...       Column 35
    Row 1:    Computer Name   User Name                 IP Address
    Row 2:    jsmith                 John_Smith      ...       192.168.21.123
    Row 3:    jsmith                 John_Smith      ...       192.168.21.67


5. Save the xls file
    MyFilePath = "\\" & "fileserver1" & "\" & "auditfolder" & "\"
    ws.Save MyFilePath & WshNetwork.ComputerName & "_" & _
        WshNetwork.UserName & "-pcaudit.xls"
    if it is the first time, then use SaveAs instead of Save

If the new row isn't added in each execution, probably count isn't updated properly or the file was not opened as suggested in step 1.

Jose
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
A short article about problems I had with the new location API and permissions in Marshmallow
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now