Page 1 of 1 1
Topic Options
#154152 - 2005-12-27 07:44 PM Inventory Script Help
Gn0stik Offline
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
#154153 - 2005-12-27 08:07 PM Re: Inventory Script Help
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Well going strictly from a guess your MAIN() function just doesn't seem right to me. If it works okay, but doesn't seem to be the best method to me. You open multiple connections to the database whereas I'd think a single open connection to the databse would be best then transfer the data during a single connect. Have not reviewed the code in depth but are you sure you're opening/closing the connections correctly in synch with what you're doing?

WMI calls don't auto refresh the object so you can't use the same object to reference new data until you remove/rebuild the object to the new reference (not saying you do or don't as I have read the code all the way through, just thought I'd bring it up)

Top
#154154 - 2005-12-27 08:16 PM Re: Inventory Script Help
Gn0stik Offline
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 Offline
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 Offline
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 Offline
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
#154158 - 2005-12-28 04:24 PM Re: Inventory Script Help
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
notepad is better.
_________________________
!

download KiXnet

Top
#154159 - 2005-12-28 05:17 PM Re: Inventory Script Help
Gn0stik Offline
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 Offline
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
#154161 - 2005-12-28 08:20 PM Re: Inventory Script Help
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
What does the -1 do ? ... -1 is just the standard COM value for TRUE, and 0 (zero) is FALSE. Are you tables indexed or not indexed ? (looking at those messages it would appear that they are, just want to ask).
Top
#154162 - 2005-12-28 08:52 PM Re: Inventory Script Help
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
Quote:

-1 is just the standard COM value for TRUE



I thought (NOT 0) was TRUE (well more TRUE than -1).
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#154163 - 2005-12-28 10:17 PM Re: Inventory Script Help
Gn0stik Offline
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
#154164 - 2005-12-28 10:31 PM Re: Inventory Script Help
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
[Les]
(well more TRUE than -1).
[/Les]

Can't argue with that Les ... NOT 0 is definitely more TRUE than -1 is.

Top
#154165 - 2005-12-29 01:43 AM Re: Inventory Script Help
Gn0stik Offline
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
#154166 - 2005-12-29 02:16 AM Re: Inventory Script Help
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Hey, that's great to hear Rich.

Thanks for posting the final code.

Top
#154167 - 2005-12-29 04:26 AM Re: Inventory Script Help
Gn0stik Offline
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
#154168 - 2005-12-31 02:23 PM Re: Inventory Script Help
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
You might want to look at the variosu dB...() UDFs, e.g. DBCommand(), or DBConnOpen(). It'll allow you to streamline your code significantly by utilizing SQL commands to query and update the database.
_________________________
There are two types of vessels, submarines and targets.

Top
#154169 - 2006-02-02 09:06 PM Re: Inventory Script Help
Savvy95 Offline
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
#154170 - 2006-03-27 08:52 PM Re: Inventory Script Help
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
i've used this code in my production environment and it works flawlessly for what i want/need. i've remarked out the stuff i don't want (netconfig, printers, & services) and added looking for keys for a program we use. Works like a champ and populates my databases perfectly. i've been working on trying to enumerate local users and creating a database for that, but so far i've been unable to get the users at all. If i do a straight WMIQuery:

----------------------------------------------------------
$userName=WMIQuery('PartComponent', 'Win32_SystemUsers') [0]

It errors out expecting a ')'. If i do a WMIQuery with variables it doesn't show any users it just shows my query and 'Press a key'

Any ideas?
Thanks,
Dave

Top
#154171 - 2006-03-27 09:00 PM Re: Inventory Script Help
Radimus Moderator Offline
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)
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.072 seconds in which 0.023 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org