#114205 - 2004-02-1705:55 PMInventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Requires: WMIQuery.
Still debugging code, but getting close..
Code:
;DB Inventory Script - Initially built by Neil Moran (February 2002)
;Modified by Kent Dyeer 17-February-2004
;Using ideas from http://www.4guysfromrolla.com/webtech/083101-1.shtml
;"Using Multiple Recordsets"
CLS
BREAK ON
DIM $x,$rc,$sql_users,$sql_computers,$sql_disks,$sql_shares,$islocaladmin,
$useridentifier,$pcidentifier,$disk,$share,$rs,$command,$dsn,$connection
$x =SETOPTION('WrapAtEOL','On')
$rc=SETOPTION('Explicit','On')
$rc=SETOPTION('NoVarsInStrings','On')
$rc=SETOPTION('CaseSensitivity','On')
;Identify the User
$useridentifier=$rs.fields("UserID").value
;Close the User Recordset
$rs.close
;Open the Computer Recordset
$sql_computers="SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='"+@wksta+"';"
$command.commandtext=$sql_computers
$rs.open($command)
;Set the Data
$rs.fields("Workstation").value=@wksta
$rs.fields("NTDomain").value=@domain
$rs.fields("SystemManufacturer").value=WMIQuery("Manufacturer","Win32_ComputerSystem")[0]
$rs.fields("SystemModel").value=WMIQuery("Model","Win32_ComputerSystem")[0]
$rs.fields("SerialNo").value=WMIQuery("SerialNumber","Win32_BIOS")[0]
$rs.fields("OS").value=@producttype
$rs.fields("ServicePack").value=@csd
$rs.fields("PhysicalMemory").value=val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration")[0])/1024
$rs.fields("ProcessorSpeed").value=val(WMIQuery("CurrentClockSpeed","Win32_Processor")[0])/1024
$rs.fields("PagefileSpace").value=val(WMIQuery("TotalPageFileSpace","Win32_LogicalMemoryConfiguration")[0])/1024
$rs.fields("VidMode").value=WMIQuery("VideoModeDescription","Win32_VideoController")[0]
$rs.fields("VidCard").value=WMIQuery("Description","Win32_VideoController")[0]
$rs.fields("Modem").value=WMIQuery("Description","Win32_POTSModem")[0]
$rs.fields("LoggedOnUser").value=@userid
$rs.fields("LastUpdate").value=@date+" "+@time
$rs.update
;ID the Computer
$pcidentifier=$rs.fields("PCID").value
;Close the RecordSet
$rs.close
;Open the Disk RecordSet
$sql_disks="SELECT * FROM TBL_DISKS WHERE COMPID="+$pcidentifier+" AND DRIVENAME='"+$name+"';"
$command.commandtext=$sql_disks
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
;Check for multiple disks
FOR EACH $disk IN WMIQuery("*","WIN32_LOGICALDISK","DRIVETYPE","3")
;Set the Data
$rs.fields("compID").value=$pcidentifier
$rs.fields("DriveName").value=$disk.name
$rs.fields("FreeSpace").value=$disk.freespace
$rs.fields("TotalSpace").value=$disk.size
$rs.fields("Format").value=$disk.filesystem
;Update on each iteration
$rs.update
;Wait - Don't close it yet
NEXT
;OK We are done now, let's close the RecordSet
$rs.close
;Collect share info on the local machine
FOR EACH $share IN WMIQuery("*","WIN32_SHARE")[0]
SELECT
CASE
$type=0
$type="Disk Drive"
CASE
$type=1
$type="Print Queue"
CASE
$type=2
$type="Device"
CASE
$type=3
$type="IPC"
CASE
$type=2147483648
$type="Disk Drive Admin"
CASE
$type=2147483649
$type="Print Queue Admin"
CASE
$type=2147483650
$type="Device Admin"
CASE
$type=2147483651
$type="IPC Admin"
ENDSELECT
;Type uint32 Read-only
;Type of resource being shared. Types include disk drives, print queues,
;interprocess communications (IPC), and general devices.
;Values are:
;0=Disk Drive
;1=Print Queue
;2=Device
;3=IPC
;2147483648=Disk Drive Admin
;2147483649=Print Queue Admin
;2147483650=Device Admin
;2147483651=IPC Admin
$sql_shares="SELECT * FROM TBL_SHARES WHERE COMPID="+$pcidentifier+" AND SHARENAME='"+$name+"'"
$command.commandtext=$sql_shares
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
$rs.fields("compID").value=$pcidentifier
$rs.fields("ShareName").value=$share.name
$rs.fields("SharePath").value=$share.path
$rs.fields("Description").value=$share.description
$rs.fields("ShareType").value=$share.type
$rs.fields("Hyperlink").value="\\"+@wksta+"\"+$name
;Update each iteration
$rs.update
;Don't close yet..
NEXT
;Close the RecordSet
$rs.close
$sql_network="SELECT * FROM TBL_NETWORKADAPTERS WHERE COMPID="+$pcidentifier+" AND DEVICEID="+$deviceid+";"
$command.commandtext=$sql_network
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
;And now grab Network Adapter info and update specific table
;$wmi_network_1="SELECT * FROM WIN32_NETWORKADAPTERCONFIGURATION WHERE IPENABLED='TRUE'"
;$netcards=GetObject($wmi).execquery($wmi_network_1)
;$netcards=WMIQuery("WIN32_NETWORKADAPTERCONFIGURATION","IPENABLED","TRUE")
;FOR EACH $card IN $netcards
FOR EACH $card IN WMIQuery("*","WIN32_NETWORKADAPTERCONFIGURATION","IPENABLED","TRUE")[0]
IF $card.dhcpenabled
$dhcp="DHCP Enabled"
ELSE
$dhcp="Static address"
ENDIF
$rs.fields("compID").value=$pcidentifier
$rs.fields("DeviceID").value=$card.index
$rs.fields("NetCard").value=$card.description
$rs.fields("IPAddress").value=@ipaddress0
$rs.fields("MACAddress").value=@address
$rs.fields("DHCP").value=$dhcp
;Update each iteration
$rs.update
;Don't close it yet
NEXT
;Now, close it
$rs.close
;Collect Printer Connection info - should grab local as well
$sql_printers="SELECT * FROM TBL_PRINTERCONNECTIONS WHERE COMPID="+$pcidentifier+" AND PRINTERID='"+WMIQuery("deviceid","WIN32_PRINTER")[0]+"';"
$command.commandtext=$sql_printers
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
FOR EACH $printer IN WMIQuery("*","WIN32_PRINTER")[0]
$rs.fields("compID").value=$pcidentifier
$rs.fields("PrinterID").value=$printer.deviceid
$rs.fields("DriverName").value=$printer.drivername
$rs.fields("PortName").value=$printer.portname
$rs.fields("Description").value=$printer.description
;Update each iteration
$rs.update
;Don't close it yet
NEXT
;Now, close
$rs.close
$sql_mappeddrives="SELECT * FROM TBL_MAPPEDDRIVES WHERE USERID="+$useridentifier+" AND LETTER='"+WMIQuery("name","WIN32_LOGICALDISK","DRIVETYPE",4)[0]+"';"
$command.commandtext=$sql_mappeddrives
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
FOR EACH $drive IN WMIQuery("*","WIN32_LOGICALDISK","DRIVETYPE",4)[0]
$rs.fields("UserID").value=$useridentifier
$rs.fields("Letter").value=$drive.name
$rs.fields("Path").value=$drive.providername
;Update each iteration
$rs.update
;Don't close yet
NEXT
;Now, close
$rs.close()
;Close all
$connection.close()
#114209 - 2004-02-1805:58 PMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Sure.. SELECT/UPDATE/INSERT would work fine.. But, I think (personally) the RecordSet Method would do just fine. You know I like to do things more difficult - kinda like chewing on tin foil.
Here is where I am having problems now..
The first section (User Info) is going thru fine.. Now, we should simply close the first RecordSet and proceed to the next. Here is what I found, but yields a Dispatch Pointer message -
Quote:
'To set the next Recordset to the same Recordset (encouraged) Set oRS = oRS.NextRecordset()
'To set the next Recordset to some other Recordset object, in order 'to still be able to access the contents of oRS Set oSomeOtherRS = oRS.NextRecordset()
See.. The whole basis of this problem lies in the fact that the script has to complete one time before the Computer, Printer, Share, and NIC info is inserted in the db.
So.. Let's drill into the error (dispatch) - Code:
$rs=$rs.NextRecordset() ;Open the Computer Recordset $sql_computers="SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='"+@wksta+"';" ;?$sql_computers ;GET $K $command.commandtext=$sql_computers $rs.open($command) ;IF @error<>0 ; @error +" and "+ @serror ; GET $k ;ENDIF
IF $rs.recordcount < 1 $rs.addnew ;? "Add new user record " + @ERROR + ": " + @SERROR Get $x ENDIF
Line 89 is the error and is - IF $rs.recordcount < 1 ...
#114212 - 2004-02-1806:37 PMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
OK.. Did some more debugging - thanks Lonk!
Code:
$rs=$rs.NextRecordset() IF @error<>0 ?"Error code is: "+@error +" and "+ @serror GET $k ENDIF ;Open the Computer Recordset $sql_computers="SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='"+@wksta+"';" ;?$sql_computers ;GET $K $command.commandtext=$sql_computers $rs.open($command) IF @error<>0 ?"Error code is: "+@error +" and "+ @serror GET $k ENDIF
And the error is -
Quote:
Error code is: -2147352567 and COM exception error "NextRecordset" (ADODB.Recordset - Current provider does not support returning multiple recordsets from a single execution.) [-2147352567/80020009]
#114213 - 2004-02-2006:23 AMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
OK.. Was able to get this to work!! What was needed to do was opened the recordset, set the data, update, and close. Then, you have to re-open the same recordset, set the variable, and close again. This was done for the $useridentifier, and $pcidentifier variables. The learning from this is the the recordset has to be cleared and reloaded.
If needed, I can post the construct for the tables for SQL Server.
#114219 - 2004-02-2202:55 AMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
While the DB Script works well, there appears to be an issue with running this with WKIX32.EXE (invisible). When you add in the DB script, the script shows.. When you comment the call to the DB script, the script does not show. I will work on Rad's suggestion.
#114220 - 2004-03-0507:35 AMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Next rev.. Trying to implement Rad's suggestion. Still needs work. Cannot get it to update the db properly.
Code:
;DB Inventory Script - Initially built by Neil Moran ;Modified by Kent Dyer 4-March-2004 ;Using ideas from http://www.4guysfromrolla.com/webtech/083101-1.shtml ;"Using Multiple Recordsets" ;Ideas from Radimus too :) CLS BREAK ON DIM $k,$x,$rc,$islocaladmin,$wmi,$name,$type,$deviceid,$card,$dhcp,$printer, $drive,$useridentifier,$pcidentifier,$disk,$share,$rs,$command,$dsn,$connection, $SystemManufacturer,$SystemModel,$SerialNo,$PhysicalMemory,$ProcessorSpeed,$PagefileSpace, $VidMode,$VidCard,$Modem,$d,$DriveName,$FreeSpace,$TotalSpace,$Format,$s,$ShareName,$SharePath, $Description,$ShareType,$Hyperlink,$n,$DeviceID,$NetCard,$DHCPE,$p,$PrinterID,$DriverName,$PortName, $Description,$m,$Letter,$path,$i
;Check for multiple disks $d=0 FOR EACH $disk IN GetObject($wmi).execquery('SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=3') $DriveName$d=$disk.name $FreeSpace$d=$disk.freespace $TotalSpace$d=$disk.size $Format$d=$disk.filesystem $d=$d+1 NEXT
$s=0 ;Collect share info on the local machine FOR EACH $share IN GetObject($wmi).execquery('SELECT * FROM WIN32_SHARE') SELECT CASE $type=0 $type='Disk Drive' CASE $type=1 $type='Print Queue' CASE $type=2 $type='Device' CASE $type=3 $type='IPC' CASE $type=2147483648 $type='Disk Drive Admin' CASE $type=2147483649 $type='Print Queue Admin' CASE $type=2147483650 $type='Device Admin' CASE $type=2147483651 $type='IPC Admin' ENDSELECT ;Type uint32 Read-only ;Type of resource being shared. Types include disk drives, print queues, ;interprocess communications (IPC), and general devices. ;Values are: ;0=Disk Drive ;1=Print Queue ;2=Device ;3=IPC ;2147483648=Disk Drive Admin ;2147483649=Print Queue Admin ;2147483650=Device Admin ;2147483651=IPC Admin
$ShareName$s=$share.name $SharePath$s=$share.path $Description$s=$share.description $ShareType$s=$share.type $Hyperlink$s='\\'+@wksta+'\'+$name $s=$s+1 NEXT
$n=0 ;And now grab Network Adapter info and update specific table FOR EACH $card IN GetObject($wmi).execquery("SELECT * FROM WIN32_NETWORKADAPTERCONFIGURATION WHERE IPENABLED='TRUE'") IF $card.dhcpenabled $dhcp+$n='DHCP Enabled' ELSE $dhcp+$n='Static address' ENDIF $DeviceID$n=$card.index $NetCard$n=$card.description $DHCPE$n=$dhcp $n=$n+1 NEXT
$p=0 ;Collect Printer Connection info - should grab local as well FOR EACH $printer IN GetObject($wmi).execquery('SELECT * FROM WIN32_PRINTER') $PrinterID$p=$printer.deviceid $DriverName$p=$printer.drivername $PortName$p=$printer.portname $Description$p=$printer.description $p=$p+1 NEXT
$m=0 FOR EACH $drive IN GetObject($wmi).execquery('SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=4') $Letter$m=$drive.name $Path$m=$drive.providername $m=$m+1 NEXT
;The problem encountered is that the ID has to be committed and closed ;before the USERID is set - it has to be reopened and then set $rs.open($command) ;Identify the User $useridentifier=$rs.fields('UserID').value $rs.close()
;Open the Computer Recordset $command.commandtext="SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='"+@wksta+"';" $rs.open($command) IF $rs.recordcount < 1 $rs.addnew ENDIF ;Set the Data $rs.fields('Workstation').value=@wksta $rs.fields('NTDomain').value=@domain $rs.fields('SystemManufacturer').value=$SystemManufacturer $rs.fields('SystemModel').value=$SystemModel $rs.fields('SerialNo').value=$SerialNo $rs.fields('OS').value=@producttype $rs.fields('ServicePack').value=@csd $rs.fields('PhysicalMemory').value=$PhysicalMemory $rs.fields('ProcessorSpeed').value=$ProcessorSpeed $rs.fields('PagefileSpace').value=$PagefileSpace $rs.fields('VidMode').value=$VidMode $rs.fields('VidCard').value=$VidCard $rs.fields('Modem').value=$Modem $rs.fields('LoggedOnUser').value=@userid $rs.fields('LastUpdate').value=@date+' '+@time $rs.update $rs.close
$rs.open($command) ;ID the Computer $pcidentifier=$rs.fields('PCID').value $rs.close
; -- COMPUTER IS DONE
;Open the Disk RecordSet $command.commandtext="SELECT * FROM TBL_DISKS WHERE COMPID="+$pcidentifier+" AND DRIVENAME='"+$disk.name+"';" $rs.open($command) FOR $i=0 to $d IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields('compID').value=$pcidentifier $rs.fields('DriveName').value=$DriveName+$i $rs.fields('FreeSpace').value=$FreeSpace+$i $rs.fields('TotalSpace').value=$TotalSpace+$i $rs.fields('Format').value=$Format+$i $rs.update $rs.close $i=$i+1 NEXT ; -- Disk Set complete
; -- Gather Shares $command.commandtext="SELECT * FROM TBL_SHARES WHERE COMPID="+$pcidentifier+" AND SHARENAME='"+$share.name+"'" $rs.open($command) FOR $i=0 to $s IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields('compID').value=$pcidentifier $rs.fields('ShareName').value=$share.name+$i $rs.fields('SharePath').value=$share.path+$i $rs.fields('Description').value=$share.description+$i $rs.fields('ShareType').value=$share.type+$i $rs.fields('Hyperlink').value='\\'+@wksta+'\'+$name+$i $rs.update $rs.close $i=$i+1 NEXT
;And now grab Network Adapter info and update specific table $command.commandtext="SELECT * FROM TBL_NETWORKADAPTERS WHERE COMPID="+$pcidentifier+" AND DEVICEID="+$card.index+";" $rs.open($command) FOR $i=0 to $n IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields('compID').value=$pcidentifier $rs.fields('DeviceID').value=$DeviceID+$i $rs.fields('NetCard').value=$NetCard+$i $rs.fields('IPAddress').value=join(split(@ipaddress0,' '),'') $rs.fields('MACAddress').value=@address $rs.fields('DHCP').value=$dhcpe+$i $rs.update $rs.close $i=$i+1 NEXT
;Collect Printer Connection info - should grab local as well $command.commandtext="SELECT * FROM TBL_PRINTERCONNECTIONS WHERE COMPID="+$pcidentifier+" AND PRINTERID='"+$printer.deviceid+"';" $rs.open($command) FOR $i=0 to $p IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields('compID').value=$pcidentifier $rs.fields('PrinterID').value=$printerid+$i $rs.fields('DriverName').value=$drivername+$i $rs.fields('PortName').value=$portname+$i $rs.fields('Description').value=$description+$i $rs.update $rs.close $i=$i+1 NEXT
$command.commandtext="SELECT * FROM TBL_MAPPEDDRIVES WHERE USERID="+$useridentifier+" AND LETTER='"+$drive.name+"';" $rs.open($command) FOR $i=0 to $m IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields('UserID').value=$useridentifier $rs.fields('Letter').value=$letter+$i $rs.fields('Path').value=$Path+$i $rs.update $rs.close() $i=$i+1 NEXT
#114221 - 2004-03-0511:37 AMRe: Inventory to DB Script
RadimusRadimus Moderator
Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
this is what I use for my Access DB Code:
$HD =WMIQuery("Size","Win32_DiskDrive") $hdGB =left($hd,len($hd)-9) $hdMBfr =GetDiskSpace("c:")/1000 $Video =WMIQuery("Description","Win32_VideoController") $Printer=WMIQuery("DriverName","Win32_Printer",,"SystemName","@wksta") $modem =WMIQuery("Description","Win32_POTSModem",,"Status","OK") $Biosv =WMIQuery("SMBIOSBIOSVersion","Win32_BIOS") $biosd =WMIQuery("Version","Win32_BIOS") $Make =split(WMIQuery("Manufacturer","Win32_ComputerSystem"))[0] $Model =WMIQuery("Model","Win32_ComputerSystem") select case instr("$model","pro") $case="Desktop" case instr("$model","evo") $case="Desktop" case instr("$model","opt") $case="Desktop" case instr("$model","lat") $case="Laptop" case 1 $case="Not Specified" endselect $asset =WMIQuery("SMBIOSAssetTag","Win32_SystemEnclosure") if instr($asset,"|") for each $return in split($asset,"|") if $return $asset=$return endif next endif $SerNo =WMIQuery("SerialNumber","Win32_BIOS") if len($SerNo) < 2 $SerNo=WMIQuery("SerialNumber","Win32_SystemEnclosure") if instr($serno,"|") for each $return in split($serno,"|") if len($return)<10 $serno=$return endif next endif endif $SerNo =trim(ucase($SerNo))
$CPUsp =WMIQuery("CurrentClockSpeed","Win32_Processor") select case left($CPUsp,2) = "26" $CPUsp=2600 case left($CPUsp,3) = "239" $CPUsp=2400 case left($CPUsp,2) = "22" $CPUsp=2200 case left($CPUsp,3) = "199" $CPUsp=2000 case left($CPUsp,3) = "179" $CPUsp=1800 case left($CPUsp,3) = "169" $CPUsp=1700 case left($CPUsp,3) = "106" $CPUsp=1100 case left($CPUsp,2) = "99" $CPUsp=1000 case left($CPUsp,2) = "90" $CPUsp=900 case left($CPUsp,2) = "85" $CPUsp=850 case left($CPUsp,2) = "70" $CPUsp=700 case left($CPUsp,2) = "66" $CPUsp=667 case left($CPUsp,2) = "59" $CPUsp=600 case left($CPUsp,2) = "49" $CPUsp=500 case left($CPUsp,2) = "39" $CPUsp=400 endselect
$dimms =Split(WMIQuery("Capacity","Win32_PhysicalMemory"),"|") for $a=0 to ubound($dimms) $=execute("$$dimm$a=val($$dimms[$a]) / 1048576") $=execute("$$memory=val($$Memory)+val($$dimm$a)") next
#114223 - 2004-03-0803:02 PMRe: Inventory to DB Script
KdyerKdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
What version are you trying to run? There are two versions in this thread. There is an Access and a SQL Server version. Rad was trying to point out to me how he achieves this. The one I posted is a conversion of one that was done a couple of years back and I am in the process of trying to hide it's execution.
I'd rather recode the inventory script to use pure SQL for database interaction as it will make the programming logic and the debugging simpler. You can then also take advanged of the various database UDFs.
_________________________
There are two types of vessels, submarines and targets.
