Jump to content

Inventory to database or excel


Dizzy
 Share

Recommended Posts

I have a VB Script that does this, and if you're familiar with VBScript and AutoIt you could probably convert it from one to the other. It is designed to only work with Windows machines (desktop and server) as written.

'*******************************************************************************
' Description: This script automates the collection of Computer information
'              for an entire subnet.
'
' Version:     1.0.0
'*******************************************************************************


'****************** - Do Not Change Anything Below This Line - *****************
'*******************************************************************************
'Global Variable Declarations
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
Dim strComputerName, strSerialNumber, strIPAddr, strWindowsVersion, strServicePack, strManuf, strModel, strUserName, strDriveSize, strMemory, strProcessor, strUserInput, strStartIP, strEndIP, objFSO, objExcel,wb
'*********************** - Configurable as Needed - ****************************
strStartIP = "192.168.0.2"
strEndIP = "192.168.0.255"
'*******************************************************************************

Set objFSO = createobject("scripting.filesystemobject")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
set wb= objExcel.Workbooks.Add()
set ws = wb.Sheets("Sheet1")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Main"

strBegTime = Now()
'Check Starting and Ending IP Addresses to make sure they are valid
strOctets = Split(strStartIP,".",4)
For count = 0 To 3
    If strOctets(count) > 255 or strOctets(count)< 0 Then
        WScript.echo "Error: Invalid Starting IP Address"
        WScript.Quit
    End If
Next
strOctets = Split(strEndIP,".",4)
For count = 0 To 3
    If strOctets(count) > 255 or strOctets(count)< 0 Then
    WScript.echo "Error: Invalid Ending IP Address"
    WScript.Quit
    End If
Next
'Checks for existance of log file - deletes if exists, the re-creates log file
If (objFSO.fileexists("C:\INVENTORY\LOGS\INVENTORY-SCAN.XLSX")) then
    objFSO.deletefile("C:\INVENTORY\LOGS\INVENTORY-SCAN.XLSX")
end If
objExcel.Columns(1).ColumnWidth = 12
objExcel.Columns(2).ColumnWidth = 22
objExcel.Columns(3).ColumnWidth = 19
objExcel.Columns(4).ColumnWidth = 15
objExcel.Columns(5).ColumnWidth = 15
objExcel.Columns(6).ColumnWidth = 16
objExcel.Columns(7).ColumnWidth = 14
objExcel.Columns(8).ColumnWidth = 32
objExcel.Columns(9).ColumnWidth = 18
objExcel.Columns(10).ColumnWidth = 15
objExcel.Cells.HorizontalAlignment = -4108


'Format cells
objExcel.Range("A1:J1").Select
objExcel.Selection.Font.bold = True
objExcel.Selection.Interior.ColorIndex = 4
objExcel.Selection.Interior.Pattern = 1
objExcel.Selection.Font.ColorIndex = 1
objExcel.Selection.Borders.Weight = 3

'Write Header information to spreadsheet
objExcel.Cells(1, 1).Value = "IP Address"
objExcel.Cells(1, 2).Value = "Host Name"
objExcel.Cells(1, 3).Value = "Windows Version"
objExcel.Cells(1, 4).Value = "Current User"
objExcel.Cells(1, 5).Value = "Manufacturer"
objExcel.Cells(1, 6).Value = "Model"
objExcel.Cells(1, 7).Value = "Serial Number"
objExcel.Cells(1, 8).Value = "Processor"
objExcel.Cells(1, 9).Value = "Physical Memory"
objExcel.Cells(1, 10).Value = "HDD Size"
Line = 2
objSheet.Cells(1, 1).Autofilter
'Set initial IP Address
strIPAddr = strStartIP
'Main Loop of Script - Check for host existance, gather info about host.
Do
    If IsPingable(strIPAddr) Then
        strExists = "True"
    Else
        strExists = "False"
    End If
    If strExists = "True" Then
        On Error Resume Next
        'attempt to connect to the c$ share of the remote computer - verifies that it is a Windows computer
        If (objFSO.folderexists("\\" & strIPAddr & "\c$")) Then
            Call subGetSerialNumber
            Call subGetHostName
            Call subGetWindowsVersion
            Call subGetServicePack
            Call subGetHardwareSpecs
            'Writes results to the SPREADSHEET
'logFile.writeLine(strIPAddr & Chr(09) & strComputerName & Chr(09) & Chr(09)& strWindowsVersion & Chr(09) & strServicePack & Chr(09) & strManuf & Chr(09) & strModel & Chr(09) & strSerialNumber & Chr(09) & strProcessor & Chr(09) & strMemory & "MB" & Chr(09) & strDriveSize & " MB")
            objExcel.Cells(Line, 1) = strIPAddr
            objExcel.Cells(Line, 2) = strComputerName
            objExcel.Cells(Line, 3) = strWindowsVersion
            objExcel.Cells(Line, 4) = strUserName
            objExcel.Cells(Line, 5) = strManuf
            objExcel.Cells(Line, 6) = strModel
            objExcel.Cells(Line, 7) = strSerialNumber
            objExcel.Cells(Line, 8) = strProcessor
            objExcel.Cells(Line, 9) = strMemory & " MB"
            objExcel.Cells(Line, 10) = strDriveSize & " MB"
            Line = Line + 1
'       Else
'           logFile.writeLine(strIPAddr & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A" & Chr(09) & "N/A")
        End If
    End If
    'Splits IP Address into four octets
    strOctets = Split(strIPAddr,".",4)
    'Increments the fourth octet
    strOctets(3) = strOctets(3) + 1
    If strOctets(3) = 255 Then
        strOctets (3) = 1
        strOctets (2) = strOctets(2) + 1
    End If
    If strOctets(2) = 255 Then
        strOctets(2) = 1
        strOctets(1) = strOctets(1) + 1
    End If
    If strOctets(1) = 255 Then
        strOctets(1) = 1
        strOctets(0) = strOctets(0) + 1
    End If
    'Re-assembles the four octets into a single IP Address
    strIPAddr = strOctets(0) & "." & strOctets(1) & "." & strOctets(2) & "." & strOctets(3)
    wscript.Echo "IP Address: " & strIPAddr
    'Clear Variable Contents for next run through the Loop.
    strComputerName = ""
    strSerialNumber = ""
    strWindowsVersion = ""
    strServicePack = ""
    strManuf = ""
    strModel = ""
    strUserName = ""
    strDriveSize = ""
    strMemory = ""
    strProcessor = ""
Loop Until strIPAddr = strEndIP
strElapsedSeconds = DateDiff("s",strBegTime,Now())
strElapsedMinutes = int(strElapsedSeconds/60)
WScript.Echo "Network Scan Completed in " & strElapsedMinutes & " minutes, " & (strElapsedSeconds-(strElapsedMinutes * 60)) & " seconds."

objExcel.ActiveWorkbook.SaveAs "C:\INVENTORY\INVENTORY-SCAN.XLSX"
objExcel.ActiveWorkbook.Close
ObjExcel.Quit
WScript.Quit


Function IsPingable(IPAddress)
    Dim UseWMI, Results
    Dim objWMI, colItems, objItem
    Dim objShell, objScriptExec
    Err.Clear
    Set objWMI = GetObject("winmgmts:\\.\root\cimv2")
    'If we have an error, just use EXE
    If Err.Number = 0 Then  'No Error
        'XP or 2003?
        Set colItems = objWMI.ExecQuery("Select Version from Win32_OperatingSystem",,48)
        For Each objItem in colItems
            UseWMI = ((Left(objItem.Version, 3) = "5.1") OR _
                (Left(objItem.Version, 3) = "5.2"))
        Next
        Set objWMI = Nothing
        Set colItems = Nothing
        Set objItem = Nothing
    Else
        'Downlevel
        Err.Clear
        UseWMI = False
    End If
    If UseWMI Then
        Set objWMI = GetObject("winmgmts:\\.\root\cimv2")
        Set colItems = objWMI.ExecQuery _
            ("SELECT StatusCode FROM Win32_PingStatus WHERE Address='" & IPAddress & "'",,48)
        For Each objItem in colItems
            IsPingable = (IsNull(objItem.StatusCode) _
                or objItem.StatusCode = 0)
        Next
        Set objWMI = Nothing
        Set colItems = Nothing
        Set objItem = Nothing
    Else
        Set objShell = CreateObject("WScript.Shell")
        Set objScriptExec = objShell.Exec("ping -n 2 -w 1000 " & IPAddress)
        Results = LCase(objScriptExec.StdOut.ReadAll)
        IsPingable = (InStr(Results, "reply from") > 0)
        Set objShell = Nothing
        Set objScriptExec = Nothing
    End If
End Function

Sub subGetSerialNumber()
    On Error Resume Next
    strSerialNumber=""
    winmgmt1 = "winmgmts:{impersonationLevel=impersonate}!//"& strIPAddr &""
    Set SNSet = GetObject( winmgmt1 ).InstancesOf ("Win32_BIOS")
    for each SN in SNSet
        strSerialNumber = SN.SerialNumber
    Next
End Sub

Sub subGetHostName()
    'Check Virus Definition Date by reading value from remote registry
    HKEY_LOCAL_MACHINE = &H80000002
    Set objReg = GetObject("winmgmts:\\" & strIPAddr & "\root\default:StdRegProv")
    'Get the remote computer name from the registry
    strKeyPath = "SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName"
    ValueName = "ComputerName"
    objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, ValueName, strValue
    strComputerName = strValue
End Sub

Sub subGetWindowsVersion()
    'Check Virus Definition Date by reading value from remote registry
    HKEY_LOCAL_MACHINE = &H80000002
    Set objReg = GetObject("winmgmts:\\" & strIPAddr & "\root\default:StdRegProv")
    'Get the remote computer name from the registry
    strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion"
    ValueName = "CurrentVersion"
    objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, ValueName, strValue
    strOSVersion = strValue
    strWindowsVersion = strOSVersion
    If strOSVersion = "5.0" Then
        strWindowsVersion = "Windows 2000"
    End If
    If strOSVersion = "5.1" Then
        strWindowsVersion = "Windows XP"
    End If
    If strOSVersion = "5.2" Then
        strWindowsVersion = "Windows 2003"
    End If
    If strOSVersion = "6.1" Then
        strWindowsVersion = "Windows 7"
    End If
End Sub

Sub subGetServicePack()
    strComputer = strIPAddr
    Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strIPAddr & "\root\cimv2")
    Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")
    For Each objOperatingSystem in colOperatingSystems
        strServicePack = objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion
    Next
End Sub

Sub subGetHardwareSpecs()
    Set objWMIService = GetObject("winmgmts:\\" & strIPAddr & "\root\CIMV2")
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
        For Each objItem In colItems
            strManuf = trim(objItem.Manufacturer)
            If strManuf = "Dell Computer Corporation" then
                strManuf = "Dell Inc."
            'Elseif strManuf = "Hewlett-Packard" then
            '   strManuf = "HP"
            end if
            strModel = trim(objItem.Model)
            If strModel = "HP EliteBook 6930p" then
                strModel = "EliteBook 6930p"
            Elseif strModel = "HP Compaq dc7900 Small Form Factor" then
                strModel = "dc7900 SFFactor"
            End If
            strUserName = trim(objItem.UserName)
            If strUserName = "" then
                strUserName = "N/A"
            end if
            strMemory = trim(objItem.TotalPhysicalMemory)
        Next
        strMemory = strMemory/1024
        strMemory = strMemory/1024
        strMemory = FormatNumber(strMemory,2)
    Set objWMIService = GetObject("winmgmts:\\" & strIPAddr & "\root\CIMV2")
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_DiskDrive", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
        For Each objItem In colItems
            strDriveSize = trim(objItem.Size)
        Next
    strDriveSize = strDriveSize/1024
    strDriveSize = strDriveSize/1024
    strDriveSize = FormatNumber(strDriveSize,2)
    Set objWMIService = GetObject("winmgmts:\\" & strIPAddr & "\root\CIMV2")
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
        For Each objItem In colItems
            strProcessor = trim(objItem.Name)
        Next
End Sub

Function UserInput( myPrompt )
    ' This function prompts the user for some input.
    ' When the script runs in CSCRIPT.EXE, StdIn is used,
    ' otherwise the VBScript InputBox( ) function is used.
    ' myPrompt is the the text used to prompt the user for input.
    ' The function returns the input typed either on StdIn or in InputBox( ).
    ' Written by Rob van der Woude
    ' http://www.robvanderwoude.com
    ' Check if the script runs in CSCRIPT.EXE
    If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
        ' If so, use StdIn and StdOut
        WScript.StdOut.Write myPrompt & " "
        UserInput = WScript.StdIn.ReadLine
    Else
        ' If not, use InputBox( )
        UserInput = InputBox( myPrompt, strTitle , Default)
    End If
End Function 

I didn't write the original parts of the script, just added the Excel parts to an already existing script. You probably don't need most of the queries in this script as it's tailored to the environment it's being used in and getting the information we needed at the time, but at least should give you a place to start from.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

IIRC there are AutoIt inventory scripts available on the forum. Search for "Inventory" in the thread title.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Or maybe >SIC2 can help.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...