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

CALL @scriptdir+'\FUNCT.KIX'
$rc=SETOPTION('WrapAtEOL','On')
$rc=SETOPTION('Explicit','On')
$rc=SETOPTION('NoVarsInStrings','On')
$rc=SETOPTION('CaseSensitivity','On')

$wmi = 'WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONATE}!//@WKSTA'

; Gather values
$SystemManufacturer=WMIQuery('Manufacturer','Win32_ComputerSystem')[0]
$SystemModel=WMIQuery('Model','Win32_ComputerSystem')[0]
$SerialNo=WMIQuery('SerialNumber','Win32_BIOS')[0]
$PhysicalMemory=val(WMIQuery('TotalPhysicalMemory','Win32_LogicalMemoryConfiguration')[0])/1024
$ProcessorSpeed=val(WMIQuery('CurrentClockSpeed','Win32_Processor')[0])/1024
$PagefileSpace=val(WMIQuery('TotalPageFileSpace','Win32_LogicalMemoryConfiguration')[0])/1024
$VidMode=WMIQuery('VideoModeDescription','Win32_VideoController')[0]
$VidCard=WMIQuery('Description','Win32_VideoController')[0]
$Modem=WMIQuery('Description','Win32_POTSModem')[0]

;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

$dsn='Driver={SQL Server};'
$dsn=$dsn+'Server=SQLSERVER;'
$dsn=$dsn+'Database=Logins;'
$dsn=$dsn+'Uid=;'
$dsn=$dsn+'Pwd='
$connection = CreateObject('ADODB.Connection')
$connection.connectionstring =$dsn
$connection.open()
$command = CreateObject('ADODB.Command')
$command.activeconnection = $connection
$rs = CreateObject('ADODB.Recordset')
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $command

$command.commandtext="SELECT * FROM TBL_USERS WHERE USERNAME='"+@userid+"';"
$rs.open($command)
IF $rs.recordcount < 1 $rs.addnew ENDIF

$rs.fields('UserName').value=@userid
$rs.fields('FullName').value=@fullname
$rs.fields('Workstation').value=@wksta
IF InGroup('\\'+@wksta+'\Administrators')
$islocaladmin='True' ELSE $islocaladmin='False' ENDIF
$rs.fields('IsLocalAdmin').value=$islocaladmin
$rs.fields('PrivilegeLevel').value=@priv
$rs.fields('HomeDrive').value=@homeshr
$rs.fields('LastUpdate').value=@date+' '+@time
$rs.fields('Description').value=@comment
$rs.update
$rs.close()

;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




;Close all
$connection.close()

;Flush all
$connection = 0
$rs = 0
$command = 0



Thanks,

Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's