#154152 - 2005-12-27 07:44 PM
Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
Long time lurker, first time poster.
Here's my situation. I've decided to write a script for doing inventory of our computers at logon. I'm currently doing it via text files and decided to have them output to a database instead. After doing a bunch of research, and borrowing code snippets from here and there, i've finally got something hammered out. There are some problems with it however. Below is posted the code for my script, i'm an ex vb and delphi programmer(years ago) so I think in functions etc, and have carried that through into my scripting, which annoys some, but please bear with me.
The problem I'm having is this: My QueryLogon, QueryHardware, QueryCPU(), and QueryOS functions work, as well as my WMIQuery funtion which is borrowed. My QueryPrinters, QueryServices, QuerySoftware, and QueryDisks functions all pull data ok, but do not write to the tables save for one, QueryDisks, which writes only one record in the disks table, instead of enumerating all. Although the data has been pulled and I have put in ? $variable name statements below the point in which they are populated, they do not get written to the table for some reason. I'm assuming it's in my database handling in those funtions, however it works above in the functions that DO work. All that is changed is the fact that it's a collection, and multiple records are added via a FOR NEXT loop. I belive my opens and rs.updates and rs.closes are in the right places. But It's not working. Any help you can offer will be greatly appreciated. I have also debugged through each function and it doesn't abort, or seem to skip anything. Also any tips regarding optimization of this script would be good as well, I have multiple declarations of the same essential variable in each function, but everything I tried to globalize them didn't work.
I can email the access db file to anyone who wishes to help me on this since I can't attach it here..
I am running kixtart 2010 on a win2000 / xp network.
Thanks in advanced, Rich
Code:
; *************Network Inventory Script By Rich Weber **************** ; * This script acquires data about the local user and computer ; * via WMI and outputs the information to a centrally located ; * MS Access or MS SQL database. Other Database engines are configurable ; * as well by changing the connection string variable $CNstring ; * all code is recyclable and free to use as you wish, and distribute, ; * as much of it was borrowed from other sources during my research of this ; * project. I just ask that if it's modified that you email me a copy ; * of your modifications, as I'd like to see any improvements, or progress ; * made on the script. Mostly for learning purposes. ; * Best Regards, Rich rich.weber@gmail.com
; ***********************Database Configuration*********************** ; *The database consist of 5 Tables; Computers, Printers, Disks, Software, ; *and services. Following are the fields that make up each ; *Computers: COMPUTERNAME, DOMAIN, SYSNAME, OSNAME, MANUFACTURER, OSVERSION ; *OSBUILD, OSTYPE, LANGUAGE, SERVICEPACK, BOOTDEVICE, SYSTEMDEVICE, WINDIR ; *SYSDIR, INSTALLDATE, LASTBOOTTIME, OSSERIAL, SYSMANUFACTURER, SYSMODEL ; *SYSSERIAL, SYSBIOSDATE, CPUMANUFACTURER, CPUDESCRIPTION, CPUSPEED, MAXCLOCK ; *FAMILY, STEPPING, DEVICEID, SYSMEMORY, VIDEOCARD, VIDEORES, NICCARD ; *MODIFYDATETIME, USERNAME ; *Disks: COMPUTERNAME, DISKNAME, VOLUME, FILESYS, DISKSIZE, FREESPACE ; *Services: COMPUTERNAME, SERVICE, DESCRIPTION, PATH, STATE ; *Software: COMPUTERNAME, APPLICATION, DESCRIPTION, VERSION, VENDOR ; *Printers: COMPUTERNAME, PRINTER, DESCRIPTION, DRIVER ; * Primary Key for all tables is COMPUTERNAME ; * You may have to play with field sized to display information properly.
; **If anyone cares to create a script for automating the database creation ; **for Access, or SQL server that would be great, if you do, shoot me an ; **email.
Dim $DBpath, $DBFile
$DBpath = @Scriptdir ;Modify this to represent your database location if using an access database. $DBfile = $DBpath +"\ITAssets.mdb"
CLS At (5,1) "Asset Audit Script Processing..." Sleep 2 CLS
Break ON CLS
If Exist($DBpath) = 0 ;Abort if no Database exists when using an access database. ? "IT Assets Database Not Found. Aborting..." Sleep 3 Exit 1 ;Else ; Main() EndIf
Dim $CNstring, $CMDtxt, $cn, $cmd, $rs
;*** CONFIGURE DATABASE CONNECTION STRING *** $CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBFile" ;Next line for sql server ;$CNstring = "Driver={SQL Server};Server=MyServer;database=MyDatabase;uid=UserName;pwd=password"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'" $cn = CreateObject ("ADODB.Connection") $cmd = CreateObject ("ADODB.Command") $rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring $cn.Open $cmd.activeconnection = $cn
QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryHardware($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryOS($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryCPU($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryDisks($cn, $cmd, $rs, $CNstring, $DBfile) QuerySoftware($cn, $cmd, $CNstring, $rs, $DBfile) QueryServices($cn, $cmd, $CNstring, $rs, $DBfile) QueryPrinters ($cn, $cmd, $CNstring, $rs, $DBfile) $cn.close Exit 1
;*** COLLECT WORKSTAION ASSET INFORMATION
Function QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $CMDtxt, $ComputerName, $ModifyDateTime, $username, $domain, $cmd, $rs ? "Querying Logon Information" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $ComputerName = @WKSTA $username = @userid $domain = @domain If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime $rs.fields.item("COMPUTERNAME").value = $computername $rs.fields.item("DOMAIN").value = $domain $rs.fields.item("USERNAME").value = $Username $rs.update $rs.Close EndFunction
Function QueryHardware($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $SysManufacturer, $SysModel, $SysSerial, $SystemBIOSVer, $SysBIOSDate Dim $SysMemory, $VideoCard, $VideoRes, $Modem, $Printers, $ptr ? "Querying Hardware" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem") $SysModel = WMIQuery("Model","Win32_ComputerSystem") $SysSerial = WMIQuery("SerialNumber","Win32_BIOS") $SystemBIOSVer = WMIQuery("SMBIOSBIOSVersion","Win32_BIOS") $SysBIOSDate = WMIQuery("Version","Win32_BIOS") $SysMemory = Val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024+1 $VideoCard = WMIQuery("Description","Win32_VideoController") $VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController") $Modem = WMIQuery("Description","Win32_POTSModem") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer $rs.fields.item("SYSMODEL").value = $SysModel $rs.fields.item("SYSSERIAL").value = $SysSerial $rs.fields.item("SYSBIOSVER").value = $SystemBIOSVer $rs.fields.item("SYSBIOSDATE").value = $SysBIOSDate $rs.fields.item("SYSMEMORY").value = $SysMemory $rs.fields.item("VIDEOCARD").value = $VideoCard $rs.fields.item("VIDEORES").value = $VideoRes ? "Querying Nic Cards" For Each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter")," ") If InStr($nic,"miniport")=0 And InStr($nic,"RAS")=0 And InStr($nic,"Parallel")=0 $NicCard = $nic EndIf $rs.fields.item("NICCARD").value = $NicCard Next $rs.Update $rs.Close EndFunction
Function QueryOS($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $OSSerial, $sysname, $osname, $SysDir, $InstallDate, $LastBoottime Dim $Manufacturer, $OSVersion, $OSBuild, $OSType, $Language, $ServicePack Dim $BootDevice, $SystemDevice, $WinDir ? "Querying OS" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $sysname = WMIQuery("CSName","Win32_OperatingSystem") $osname = WMIQuery("Caption","Win32_OperatingSystem") $Manufacturer = WMIQuery("Manufacturer","Win32_OperatingSystem") $OSVersion = WMIQuery("Version","Win32_OperatingSystem") $OSBuild = WMIQuery("BuildNumber","Win32_OperatingSystem") $OSType = WMIQuery("BuildType","Win32_OperatingSystem") $Language = WMIQuery("OSLanguage","Win32_OperatingSystem") $ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem") $BootDevice = WMIQuery("BootDevice","Win32_OperatingSystem") $SystemDevice = WMIQuery("SystemDevice","Win32_OperatingSystem") $WinDir = WMIQuery("WindowsDirectory","Win32_OperatingSystem") $SysDir = WMIQuery("SystemDirectory","Win32_OperatingSystem") $InstallDate = Int(WMIQuery("InstallDate","Win32_OperatingSystem")) $LastBoottime = Int(WMIQuery("LastBootupTime","Win32_OperatingSystem")) $OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("SYSNAME").value = $Sysname $rs.fields.item("OSNAME").value = $osname $rs.fields.item("MANUFACTURER").value = $Manufacturer $rs.fields.item("OSVERSION").value = $OSVersion $rs.fields.item("OSBUILD").value = $OSBuild $rs.fields.item("OSTYPE").value = $OSType $rs.fields.item("LANGUAGE").value = $Language $rs.fields.item("SERVICEPACK").value = $ServicePack $rs.fields.item("BOOTDEVICE").value = $BootDevice $rs.fields.item("SYSTEMDEVICE").value = $SystemDevice $rs.fields.item("WINDIR").value = $WinDir $rs.fields.item("SYSDIR").value = $SysDir $rs.fields.item("INSTALLDATE").value = $InstallDate $rs.fields.item("LASTBOOTTIME").value = $LastBoottime $rs.fields.item("OSSERIAL").value = $OSSerial $rs.update $rs.Close EndFunction
Function QueryCPU($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) ? "Querying CPU" Dim $CPUManufacturer, $CPUDescription, $CPUSpeed Dim $DeviceID, $CMDtxt, $MaxClock, $Family, $Stepping $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $CPUManufacturer = WMIQuery("manufacturer","Win32_Processor") $CPUDescription = WMIQuery("description","Win32_Processor") $CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor") $MaxClock = WMIQuery("maxclockspeed","Win32_Processor") $Family = WMIQuery("family","Win32_Processor") $Stepping = WMIQuery("stepping","Win32_Processor") $DeviceID = WMIQuery("deviceid","Win32_Processor") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("CPUMANUFACTURER").value = $CPUManufacturer $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription $rs.fields.item("CPUSPEED").value = $CPUSpeed $rs.fields.item("MAXCLOCK").value = $MaxClock $rs.fields.item("FAMILY").value = $Family $rs.fields.item("STEPPING").value = $Stepping $rs.fields.item("DEVICEID").value = $DeviceID $rs.update $rs.Close EndFunction
Function QueryDisks($cn, $cmd, $rs, $CNstring, $DBfile) Dim $CMDtxt, $Item, $Disks, $Drivetype $CMDtxt = "select * from DISKS where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Disk Drives" $Disks = $wmiRoot.ExecQuery("Select * FROM Win32_LogicalDisk WHERE DriveType=3") For Each $Item in $Disks $rs.addnew $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("DISKNAME").value = $Item.Name $rs.fields.item("VOLNAME").value = $Item.VolumeName $rs.fields.item("DISKSIZE").value = $Item.Size $rs.fields.item("FILESYS").value = $Item.Filesystem $rs.fields.item("FREESPACE").value = $Item.Freespace $rs.Update Next $rs.Close EndFunction
Function QuerySoftware($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $wmiRoot, $Item $CMDtxt = "select * from SOFTWARE where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Software" $Applicatons = $wmiRoot.ExecQuery("Select * FROM Win32_Product") For Each $Item in $Applications $rs.addnew $rs.fields.item("COMPUTERNAME").value = $@WKSTA $rs.fields.item("APPLICATION").value = $Item.Caption $rs.Fields.item("DESCRIPTION").value = $Item.Description $rs.fields.item("VERSION").value = $Item.Version $rs.fields.item("VENDOR").value = $Item.Vendor $rs.Update Next $rs.Close EndFunction
Function QueryServices($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $Services, $Item, $wmiRoot $CMDtxt = "select * from SERVICES where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Services" $Services = $wmiRoot.ExecQuery("Select * FROM Win32_Service") For Each $Item in $Services $rs.addnew $rs.Fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("SERVICE").value = $Item.Name $rs.fields.item("DESCRIPTION").value = $Item.Description $rs.fields.item("PATH").value = $Item.PathName $rs.update Next $rs.Close EndFunction
Function QueryPrinters($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $wmiRoot, $Printers, $Item $CMDtxt = "select * from PRINTERS where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Printers" $Printers = $wmiRoot.ExecQuery("Select * FROM Win32_Printer") For Each $Item in $Printers $rs.addnew $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("PRINTER").value = $Item.Caption $rs.fields.item("DESCRIPTION").value = $Item.Description $rs.fields.item("DRIVER").value = $Item.DriverName Next $rs.Update $rs.Close EndFunction
Function WMIQuery($what,$where,) Dim $strQuery, $objEnumerator, $value $strQuery = "Select $what From $where" $SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//@WKSTA") $objEnumerator = $SystemSet.ExecQuery($strQuery) For Each $objInstance in $objEnumerator If @Error = 0 And $objInstance <> "" $=Execute("$$value = $$objInstance.$what") $WMIQuery="$value"+" "+"$WMIQuery" EndIf Next $WMIQuery=Left($WMIQuery,Len($WMIQuery)-1) Exit @error EndFunction
|
Top
|
|
|
|
#154154 - 2005-12-27 08:16 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
Well I tried doing it by opening the connection in the beginning, and closing it at the end, before I implemented the main function and I couldn't get it to work. If I could make the database connection declarations visible to all of the functions (except CMDtxt) that would be possible, is there a way to do that? Using Global just errors out.
Ok, after looking closer, I realized I had multiple cn.opens, and no cn.closes. I have fixed that, and gotten rid of the main function, but no change. The tables still aren't being updated for the functions in question.. I edited my original post to reflect the change.
|
Top
|
|
|
|
#154155 - 2005-12-28 03:45 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
Any Help would be appreciated here guys. I know you guys are helpful and have seen you help people on these kinds of things before. Jens, Shawn? Anyone?
I'm at a loss here.
|
Top
|
|
|
|
#154156 - 2005-12-28 03:52 PM
Re: Inventory Script Help
|
Shawn
Administrator
Registered: 1999-08-13
Posts: 8611
|
What happens if you throw a tracer after the AddNew and Update ?
Code:
For Each $Item in $Disks $rs.addnew ?">>>ADD @ERROR : @SERROR" $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("DISKNAME").value = $Item.Name $rs.fields.item("VOLNAME").value = $Item.VolumeName $rs.fields.item("DISKSIZE").value = $Item.Size $rs.fields.item("FILESYS").value = $Item.Filesystem $rs.fields.item("FREESPACE").value = $Item.Freespace $rs.Update ?">>>UPDATE @ERROR : @SERROR" Next
btw - you may need to $=setoption("wrapateol","on") to see the whole message.
|
Top
|
|
|
|
#154157 - 2005-12-28 03:58 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
I'll try that and get back to you. I'll rem out the other functions and debug through it. Man I wish I had ASE, I'm almost done with the trial :/
|
Top
|
|
|
|
#154159 - 2005-12-28 05:17 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
OK, Here's what happened. (sorry it took so long, I had a commute to deal with)
The output was: >>>ADD 0 : The operation completed successfully.
>>>UPDATE -2147352567 : COM exception error "Update" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.) [-2147352567/80020009]
>>>ADD -2147352567 : COM exception error "addnew" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.) [-2147352567/80020009]
>>>ADD -2147352567 : COM exception error "addnew" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.) [-2147352567/80020009]
So it appears I have a configuration problem in my DB. I need to work on the index? (I'm not a database designer, I know little more than the fact that they have tables and fields and datatypes for said fields.) I took a class years ago, but that was a looooong time ago)
Is there a place I can go to look up all the nitty gritty about working with the adodb com object?
Update: I removed the PK, now it makes it through the second and third iterations, however, I get a duplicate of the C drive record. Perhaps if I place the PK on a different field I can fix that or create an index which would force unique entries somehow. I'm lacking in my knowlege of this stuff.
Edited by Gn0stik (2005-12-28 05:32 PM)
|
Top
|
|
|
|
#154160 - 2005-12-28 05:52 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
OK, I found the ADO api reference at MSDN. I looked up info on the recorset object, and adjusted my code a bit working on the assumption that the rs.addnew needed a conditional frame around it. The "if rs.eof = -1" has been used a lot, so I thought I'd see how that worked. It worked ok, however I went and checked it out, and all I can see for eof as possible values is "true" and "false". What does the -1 do? I saw commenting that said it checked to see if the record already exists, but I can't see how it does that programmatically. At any rate now it looks like this:
Code:
For Each $Item in $Disks If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("DISKNAME").value = $Item.Name $rs.fields.item("VOLNAME").value = $Item.VolumeName $rs.fields.item("DISKSIZE").value = $Item.Size $rs.fields.item("FILESYS").value = $Item.Filesystem $rs.fields.item("FREESPACE").value = $Item.Freespace $rs.Movenext Next $rs.Update
And it seems to work. Can anyone foresee anything going wrong with this setup?
|
Top
|
|
|
|
#154163 - 2005-12-28 10:17 PM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
They are indexed by computer name and whatever the caption field is. But the reason I was getting the error was that I had a primary key set on the computername field in some tables that needed duplicate entries for that, so that I could pull counts based on it etc in reports.
So I indexed them and made them foreign keys to the master computer table's primary which is COMPUTERNAME with a one-to-many relationship.
I really need to read up on my database design crap.
I'm currently redesigning my software query and niccard query functions neither of which work right now even with your tips. However, you folks helped me get the services, printers, and disks related functions working perfectly.
|
Top
|
|
|
|
#154165 - 2005-12-29 01:43 AM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
All is working well now. Thanks for all the help, I'll give you all props in the comments. Here is the final code.
Code:
; *************Network Inventory Script By Rich Weber **************** ; * This script acquires data about the local user and computer ; * via WMI and outputs the information to a centrally located ; * MS Access or MS SQL database. Other Database engines are configurable ; * as well by changing the connection string variable $CNstring ; * all code is recyclable and free to use as you wish, and distribute, ; * as much of it was borrowed from other sources during my research of this ; * project. I just ask that if it's modified that you email me a copy ; * of your modifications, as I'd like to see any improvements, or progress ; * made on the script. Mostly for learning purposes. ; * Many thanks to all the helpful souls at Kixtart.org forums. Special ; * thanks to Shawn, and NTDOC. Again, Please let me know about any ; * improvements that you make, or need to be made. I am open to critisizm. ; * Best Regards, Rich rich.weber@gmail.com
; ***********************Database Configuration*********************** ; *The database consist of 5 Tables; Computers, Printers, Disks, Software, ; *and services. Following are the fields that make up each:
; *Computers: COMPUTERNAME, USERNAME, DOMAIN, MODIFYDATETIME, SYSNAME, OSNAME ; MANUFACTURER, OSVERSIONOSBUILD, OSTYPE, LANGUAGE, SERVICEPACK, BOOTDEVICE ; SYSTEMDEVICE, WINDIR, SYSDIR, OSSERIAL, SYSMANUFACTURER ; SYSMODEL, SYSBIOSDATE, CPUMANUFACTURER, CPUDESCRIPTION, CPUSPEED, MAXCLOCK ; FAMILY, STEPPING, DEVICEID, SYSMEMORY, VIDEOCARD, VIDEORES, NICCARD ; *Disks: COMPUTERNAME, DISKNAME, VOLUME, FILESYS, DISKSIZE, FREESPACE ; *Services: COMPUTERNAME, SERVICE, DESCRIPTION, PATH, STATE ; *Software: COMPUTERNAME, APPLICATION, DESCRIPTION, VERSION, VENDOR ; *Printers: COMPUTERNAME, PRINTER, DESCRIPTION, DRIVER ; *Netconfig: COMPUTERNAME, NICCID, IPADDRESS, IPSUBNET, MACADDRESS, SERVICENAME, DESCRIPTION
; * You may have to play with field sizes to display information properly.
; **If anyone cares to create a script for automating the database creation ; **for Access, or SQL server that would be great, if you do, shoot me an ; **email. $=setoption("wrapateol","on") Dim $DBpath, $DBFile
$DBpath = @Scriptdir ;Modify this to represent your database location if using an access database. $DBfile = $DBpath +"\ITAssets.mdb" CLS At (5,1) "Asset Audit Script Processing..." Sleep 2 CLS
Break ON CLS
If Exist($DBpath) = 0 ;Abort if no Database exists when using an access database. ? "IT Assets Database Not Found. Aborting..." Sleep 3 Exit 1 Else Main($DBFile) EndIf
Function Main($DBFile) Dim $CNstring, $CMDtxt, $cn, $cmd, $rs
;*** CONFIGURE DATABASE CONNECTION STRING *** $CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBFile" ;Next line for sql server ;$CNstring = "Driver={SQL Server};Server=MyServer;database=MyDatabase;uid=UserName;pwd=password"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'" $cn = CreateObject ("ADODB.Connection") $cmd = CreateObject ("ADODB.Command") $rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring $cn.Open $cmd.activeconnection = $cn
QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryHardware($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryCPU($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QueryNetConfig($cn, $cmd, $CNstring, $rs, $DBfile) QueryDisks($cn, $cmd, $rs, $CNstring, $DBfile) QueryPrinters($cn, $cmd, $CNstring, $rs, $DBfile) QueryOS($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) QuerySoftware($cn, $cmd, $CNstring, $rs, $DBfile) QueryServices($cn, $cmd, $CNstring, $rs, $DBfile)
$cn.close Exit 1 EndFunction
;*** COLLECT WORKSTAION ASSET INFORMATION
;Queries Logon information, updates COMPUTERS table. Function QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $CMDtxt, $ComputerName, $ModifyDateTime, $username, $domain, $cmd, $rs ? "Querying Logon Information" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $ComputerName = @WKSTA $username = @userid $domain = @domain $ModifyDateTime = @DATE + " " + @TIME If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime $rs.fields.item("COMPUTERNAME").value = $computername $rs.fields.item("DOMAIN").value = $domain $rs.fields.item("USERNAME").value = $Username $rs.update $rs.Close EndFunction
;Queries Hardware, updates COMPUTERS table. Function QueryHardware($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $SysManufacturer, $SysModel, $SysSerial, $SystemBIOSVer, $SysBIOSDate Dim $SysMemory, $VideoCard, $VideoRes, $Modem, $Printers, $ptr ? "Querying Hardware" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem") $SysModel = WMIQuery("Model","Win32_ComputerSystem") $SysSerial = WMIQuery("SerialNumber","Win32_BIOS") $SystemBIOSVer = WMIQuery("SMBIOSBIOSVersion","Win32_BIOS") $SysBIOSDate = WMIQuery("Version","Win32_BIOS") $SysMemory = Val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024+1 $VideoCard = WMIQuery("Description","Win32_VideoController") $VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController") $Modem = WMIQuery("Description","Win32_POTSModem") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer $rs.fields.item("SYSMODEL").value = $SysModel $rs.fields.item("SYSBIOSVER").value = $SystemBIOSVer $rs.fields.item("SYSBIOSDATE").value = $SysBIOSDate $rs.fields.item("SYSMEMORY").value = $SysMemory $rs.fields.item("VIDEOCARD").value = $VideoCard $rs.fields.item("VIDEORES").value = $VideoRes $rs.Update $rs.Close EndFunction
;Gathers info about installed CPUs, updates COMPUTERS Table ;currently only single cpu supported, but easy to break out to multiple in for each. Function QueryCPU($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) ? "Querying CPU" Dim $CPUManufacturer, $CPUDescription, $CPUSpeed Dim $DeviceID, $CMDtxt, $MaxClock, $Family, $Stepping $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $CPUManufacturer = WMIQuery("manufacturer","Win32_Processor") $CPUDescription = WMIQuery("description","Win32_Processor") $CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor") $MaxClock = WMIQuery("maxclockspeed","Win32_Processor") $Family = WMIQuery("family","Win32_Processor") $Stepping = WMIQuery("stepping","Win32_Processor") $DeviceID = WMIQuery("deviceid","Win32_Processor") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("CPUMANUFACTURER").value = $CPUManufacturer $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription $rs.fields.item("CPUSPEED").value = $CPUSpeed $rs.fields.item("MAXCLOCK").value = $MaxClock $rs.fields.item("FAMILY").value = $Family $rs.fields.item("STEPPING").value = $Stepping $rs.fields.item("DEVICEID").value = $DeviceID $rs.update $rs.Close EndFunction
;Queries Network Adapters and gathers network configuration, updates NETCONFIG table. Function QueryNetconfig($cn, $cmd, $CNstring, $rs, $DBfile) DIM $CMDtxt, $wmiRoot, $IP, $item, $Ivalue, $i $CMDtxt = "select * from NETCONFIG where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open($cmd) $ncount = 0 ?"Querying Network Adapters" $wmiRoot = GetObject("WinMgmts:root/cimv2") $IP = $wmiRoot.ExecQuery("Select * from Win32_NetworkAdapterConfiguration where IPEnabled = true") For Each $item In $IP For $i = 0 to Ubound($item.IPAddress) If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("NICID").value = $i $rs.fields.item("IPADDRESS").value = $item.IPAddress($i) $rs.fields.item("SUBNETMASK").value = $item.IPsubnet($i) $rs.fields.item("MACADDRESS").value = $item.MACAddress $rs.fields.item("SERVICENAME").Value = $item.ServiceName $rs.fields.item("DESCRIPTION").value = Trim(Substr($item.caption, 11, Len($item.caption)-10)) $rs.MoveNext $ncount=$ncount + 1 Next Next $rs.Update $CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'" $rs.fields.item("NICCOUNT").value = $ncount $rs.update $rs.Close EndFunction
;Gathers information about Logical Disks, updates DISKS table. Function QueryDisks($cn, $cmd, $rs, $CNstring, $DBfile) Dim $CMDtxt, $Item, $Disks, $Drivetype $CMDtxt = "select * from DISKS where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Disk Drives" $Disks = $wmiRoot.ExecQuery("Select * FROM Win32_LogicalDisk WHERE DriveType=3") For Each $Item in $Disks If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("DISKNAME").value = $Item.Name $rs.fields.item("VOLNAME").value = $Item.VolumeName $rs.fields.item("DISKSIZE").value = $Item.Size $rs.fields.item("FILESYS").value = $Item.Filesystem $rs.fields.item("FREESPACE").value = $Item.Freespace $rs.Movenext Next $rs.Update $rs.Close EndFunction
;Gathers information about printers, updates PRINTERS Table. Function QueryPrinters($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $wmiRoot, $Printers, $Item $CMDtxt = "select * from PRINTERS where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Printers" $Printers = $wmiRoot.ExecQuery("Select * FROM Win32_Printer") For Each $Item in $Printers If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("PRINTER").value = $Item.Caption $rs.fields.item("DESCRIPTION").value = $Item.Description $rs.fields.item("DRIVER").value = $Item.DriverName $rs.MoveNext Next $rs.Update $rs.Close EndFunction
;Queries OS via WMI, updates COMPUTERS table. Function QueryOS($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile) Dim $OSSerial, $sysname, $osname, $SysDir, $Manufacturer Dim $OSVersion, $OSBuild, $OSType, $Language, $ServicePack Dim $BootDevice, $SystemDevice, $WinDir ? "Querying OS" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $sysname = WMIQuery("CSName","Win32_OperatingSystem") $osname = WMIQuery("Caption","Win32_OperatingSystem") $Manufacturer = WMIQuery("Manufacturer","Win32_OperatingSystem") $OSVersion = WMIQuery("Version","Win32_OperatingSystem") $OSBuild = WMIQuery("BuildNumber","Win32_OperatingSystem") $OSType = WMIQuery("BuildType","Win32_OperatingSystem") $Language = WMIQuery("OSLanguage","Win32_OperatingSystem") $ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem") $BootDevice = WMIQuery("BootDevice","Win32_OperatingSystem") $SystemDevice = WMIQuery("SystemDevice","Win32_OperatingSystem") $WinDir = WMIQuery("WindowsDirectory","Win32_OperatingSystem") $SysDir = WMIQuery("SystemDirectory","Win32_OperatingSystem") $OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem") If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("SYSNAME").value = $Sysname $rs.fields.item("OSNAME").value = $osname $rs.fields.item("MANUFACTURER").value = $Manufacturer $rs.fields.item("OSVERSION").value = $OSVersion $rs.fields.item("OSBUILD").value = $OSBuild $rs.fields.item("OSTYPE").value = $OSType $rs.fields.item("LANGUAGE").value = $Language $rs.fields.item("SERVICEPACK").value = $ServicePack $rs.fields.item("BOOTDEVICE").value = $BootDevice $rs.fields.item("SYSTEMDEVICE").value = $SystemDevice $rs.fields.item("WINDIR").value = $WinDir $rs.fields.item("SYSDIR").value = $SysDir $rs.fields.item("OSSERIAL").value = $OSSerial $rs.update $rs.Close EndFunction
;Pulls information about installed software from the Registry, updates SOFTWARE table. Function QuerySoftware($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $Index, $KeyName, $Application, $Version, $Publisher $CMDtxt = "select * from SOFTWARE where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) ? "Querying Installed Software"
$Index = 0 $KeyName = EnumKey("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall", $Index) $Application = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayName") $Version = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayVersion") $Vendor = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "Publisher")
While Len($keyname) > 0 If Len($Application)> 0 If $rs.eof = -1 $rs.addnew EndIf $rs.fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("APPLICATION").value = $Keyname $rs.fields.item("VERSION").value = $Version $rs.fields.item("VENDOR").value = $Vendor $rs.fields.item("DESCRIPTION").value = $Application $rs.MoveNext EndIf
$Index = $Index + 1 $KeyName = EnumKey("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall", $Index) $Application = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayName") $Version = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayVersion") $Vendor = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "Publisher") Loop $rs.update $rs.Close EndFunction
;Queries WMI for services and their properties, updates SERVICES table. Function QueryServices($cn, $cmd, $CNstring, $rs, $DBfile) Dim $CMDtxt, $Services, $Item, $wmiRoot $CMDtxt = "select * from SERVICES where computername = '@WKSTA'" $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $wmiRoot = GetObject("WinMgmts:root/cimv2") ? "Querying Services" $Services = $wmiRoot.ExecQuery("Select * FROM Win32_Service") For Each $Item in $Services If $rs.eof = -1 $rs.addnew EndIf $rs.Fields.item("COMPUTERNAME").value = @WKSTA $rs.fields.item("SERVICE").value = $Item.Name $rs.fields.item("DESCRIPTION").value = $Item.Description $rs.fields.item("PATH").value = $Item.PathName $rs.fields.item("STARTMODE").value = $Item.Startmode $rs.fields.item("STATE").value = $Item.State $rs.MoveNext Next $rs.Update $rs.Close EndFunction
;Used for gathering Info from WMI, Just pass it the Item and Object Function WMIQuery($what,$where,) Dim $strQuery, $objEnumerator, $value $strQuery = "Select $what From $where" $SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//@WKSTA") $objEnumerator = $SystemSet.ExecQuery($strQuery) For Each $objInstance in $objEnumerator If @Error = 0 And $objInstance <> "" $=Execute("$$value = $$objInstance.$what") $WMIQuery="$value"+"|"+"$WMIQuery" EndIf Next $WMIQuery=Left($WMIQuery,Len($WMIQuery)-1) Exit @error EndFunction
|
Top
|
|
|
|
#154167 - 2005-12-29 04:26 AM
Re: Inventory Script Help
|
Gn0stik
Fresh Scripter
Registered: 2005-12-27
Posts: 12
|
No problem man, you guys were great help. I couldn't have done it without you. I got some really good tips for debugging and finding out what my problems were. Of course there are countless unnamed helpers who worked on the various sample scripts I drew my inspiration from.
|
Top
|
|
|
|
#154169 - 2006-02-02 09:06 PM
Re: Inventory Script Help
|
Savvy95
Fresh Scripter
Registered: 2005-05-09
Posts: 11
|
That's a fantastic script. I do have a problem though inserting records into 2 Access tables. The other 3 work fine.
The software and services tables are never populated. I have noticed that if ANY variable such as @HOMEDIR or @HOMEDRIVE are empty, nothing is inserted into the tables. I tested this for the Computers table.
Does anybody have any suggestions?
|
Top
|
|
|
|
#154171 - 2006-03-27 09:00 PM
Re: Inventory Script Help
|
Radimus
Moderator
Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
|
BTW.. do not index on computername, as the cane can change. Use serialnumber instead (if your PCs have them)
|
Top
|
|
|
|
Moderator: Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart
|
0 registered
and 259 anonymous users online.
|
|
|