Script to identify mobile devices and registered owner

Posted on 2011-10-26
Last Modified: 2012-05-12
Hi all

Please help; we're working on a large project with a somewhat unknown estate.

I'm ideally looking for a piece of code to work with an input text file of hostnames of devices. Then run a WMI query to for each which interprets the registered owner and Win32_SystemEnclosure class type to show up anything other than a desktop and then pipe out to another file for review.

Many thanks in advance
Question by:samymac

    Author Comment

    Okay I'm sure this is possible and have found a piece of code to start me off hacking.

    It has a couple of undesirables in that it stores the data vertically rather than horizontally within excel and it also opens a new spreadsheet rather than appending the data under one sheet.

    Points still available for anyone who can improve
    Option Explicit 
    On error resume next
    Dim objFSO, objExcel, objWMIService, colItems, objItem 
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
    Const ForReading = 1 
    Dim objDomain, fso, tsInputFile, strLine, arrInput, strComputer 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Set tsInputFile = fso.OpenTextFile("customercare.txt", ForReading, False) 
    While Not tsInputFile.AtEndOfStream 
      strLine = tsInputFile.ReadLine 
      arrInput = Split(strLine, ";") 
     strComputer = arrInput(0) 
     Scanpc (strComputer) 
    WScript.Echo "Finished" 
    Sub scanpc(strComputer) 
    Set objExcel = CreateObject("Excel.Application")  
    objExcel.Visible = True  
    objExcel.Cells(1, 1).Value = "Name" 
     objExcel.Cells(2, 1).Value = "Caption" 
     objExcel.Cells(3, 1).Value = "Version" 
     objExcel.Cells(4, 1).Value = "Registered User"   
    objExcel.Cells(5, 1).Value = "Serial Number"   
    objExcel.Cells(6, 1).Value = "CSD Version"   
    objExcel.Cells(7, 1).Value = "Description"   
    objExcel.Cells(8, 1).Value = "Last Boot Up Time"   
    objExcel.Cells(9, 1).Value = "Local Date Time"   
    objExcel.Cells(10, 1).Value = "Organization"   
    objExcel.Cells(11, 1).Value = "Domain"   
    objExcel.Cells(12, 1).Value = "Manufacturer"   
    objExcel.Cells(13, 1).Value = "Model"   
    objExcel.Cells(14, 1).Value = "Number Of Processors"   
    objExcel.Cells(15, 1).Value = "Primary Owner Name" 
     objExcel.Cells(16, 1).Value = "System Type" 
     objExcel.Cells(17, 1).Value = "Total Physical Memory" 
     objExcel.Cells(18, 1).Value = "User Name" 
     objExcel.Cells(19, 1).Value = "Caption" 
     objExcel.Cells(20, 1).Value = "Manufacturer" 
     objExcel.Cells(21, 1).Value = "Name" 
     objExcel.Cells(22, 1).Value = "Release Date" 
     objExcel.Cells(23, 1).Value = "Serial Number" 
     objExcel.Cells(24, 1).Value = "SMBIOS BIOS Version" 
     objExcel.Cells(25, 1).Value = "Version" 
     Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
     Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem") 
     For Each objItem in colItems 
     objExcel.Cells(1, 2).Value = objItem.CSName 
     objExcel.Cells(2, 2).Value = objItem.Caption 
     objExcel.Cells(3, 2).Value = objItem.Version 
     objExcel.Cells(4, 2).Value = objItem.RegisteredUser 
     objExcel.Cells(5, 2).Value = objItem.SerialNumber 
     objExcel.Cells(6, 2).Value = objItem.CSDVersion 
     objExcel.Cells(7, 2).Value = objItem.Description 
     objExcel.Cells(8, 2).Value = objItem.LastBootUpTime 
     objExcel.Cells(9, 2).Value = objItem.LocalDateTime 
     objExcel.Cells(10, 2).Value = objItem.Organization 
     Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem") 
     For Each objItem in colItems 
      objExcel.Cells(11, 2).Value = objItem.Domain 
     objExcel.Cells(12, 2).Value = objItem.Manufacturer 
     objExcel.Cells(13, 2).Value = objItem.Model 
     objExcel.Cells(14, 2).Value = objItem.NumberOfProcessors 
     objExcel.Cells(15, 2).Value = objItem.PrimaryOwnerName 
     objExcel.Cells(16, 2).Value = objItem.SystemType 
     objExcel.Cells(17, 2).Value = (objItem.TotalPhysicalMemory /1024) & " MB" 
     objExcel.Cells(18, 2).Value = objItem.UserName 
     Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
     Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS") 
     For Each objItem in colItems 
     objExcel.Cells(19, 2).Value = objItem.Caption 
     objExcel.Cells(20, 2).Value = objItem.Manufacturer 
     objExcel.Cells(21, 2).Value = objItem.Name 
     objExcel.Cells(22, 2).Value = objItem.ReleaseDate 
     objExcel.Cells(23, 2).Value = objItem.SerialNumber 
     objExcel.Cells(24, 2).Value = objItem.SMBIOSBIOSVersion 
     objExcel.Cells(25, 2).Value = objItem.Version 
     objExcel.Selection.Font.ColorIndex = 11 
     objExcel.Selection.Font.Bold = True 
    ' MsgBox "Done" 
    end Sub

    Open in new window

    LVL 43

    Expert Comment

    by:Steve Knight
    Would doing it in Excel (i.e. VBA code which can take your text file and do the lookups) be OK, or a VBScript like you have?

    In Excel you could soon have a button which runs a VBA macro to prompt for a text file then query those computers and update a sheet... or you could just keep those computer names in the sheet in the first place which would be simpler, i.e. it runs down column A, find the computer names there and fills in / udpates the rest?

    Having got the data in the sheets you can soon use auto-filter or pivot tables or whatever to summarise it.

    LVL 43

    Expert Comment

    by:Steve Knight
    Here is an example of what I mean you can do simply in VBA:

    LVL 43

    Expert Comment

    by:Steve Knight
    BTW could add the "MSDOS" zone area to the list if you decide to select this as an answer!

    Author Comment

    Lovely stuff.

    Dragon this is what I'm looking for... only it bombs out when a device name isn't contactable, do you know I can remedy this?

    I've tested on a few machines around me and it works, so I assume its that it can resolve names or something.
    LVL 43

    Accepted Solution

    Good point.  Hasn't put any error handling in yet.  Try this one.  It will pause and hang when it gets to a computer it can't talk to then put the error message in second column.


    Author Closing Comment

    Thanks again
    LVL 43

    Expert Comment

    by:Steve Knight
    np, good luck with it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    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.

    Suggested Solutions

    This article was inspired by a question here at Experts Exchange ( The requirements stated in that question are (1) reduce the file size of a large number of…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now