| Kdyer |
| (KiX Supporter) |
| 2004-02-17 05:55 PM |
|
Inventory to DB Script
|
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')
$dsn="Driver={SQL Server};"
$dsn=$dsn+"Server=SERVER;"
$dsn=$dsn+"Database=Logins;"
$dsn=$dsn+"Uid=;"
$dsn=$dsn+"Pwd="
$connection=CreateObject("ADODB.Connection")
$command=CreateObject("ADODB.Command")
$rs=CreateObject("ADODB.Recordset")
$connection.connectionstring=$dsn
$connection.open()
$command.activeconnection=$connection
$rs.cursortype=3
$rs.locktype=3
$rs.activecommand=$command
$sql_users="SELECT * FROM TBL_USERS WHERE USERNAME='"+@userid+"';"
$command.commandtext=$sql_users
$rs.open($command)
IF $rs.recordcount < 1
$rs.addnew
;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
ENDIF
;$rs.Open $sql_users, $dsn
IF InGroup("\\"+@wksta+"\Administrators")
$islocaladmin="True"
ELSE
$islocaladmin="False"
ENDIF
$rs.fields("UserName").value=@userid
$rs.fields("FullName").value=@fullname
$rs.fields("Workstation").value=@wksta
$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
;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()
;Flush all
$connection = 0
$rs = 0
$command = 0
Thanks,
Kent
Lonkero
|
| (KiX Master Guru) |
| 2004-02-18 02:54 AM |
|
Re: Inventory to DB Script
|
eh, did I get it...
instead of update, it adds new ones?
sounds like it ain't closing or something...
| Kdyer |
| (KiX Supporter) |
| 2004-02-18 02:26 PM |
|
|
|
Re: Inventory to DB Script
|
OK..
Let's try a PostPrepped version of this. Also, WMIQuery seems to have a hard time with a "*".. Please correct me if I am wrong.
Thanks,
Kent
| Sealeopard |
| (KiX Master) |
| 2004-02-18 04:27 PM |
|
|
Re: Inventory to DB Script
|
I'd convert it to pure SQL code with regards to the database SELECT/UPDATE/INSERT INTO stuff and use the DB*() UDFs. Might lead to cleaner code.
| Kdyer |
| (KiX Supporter) |
| 2004-02-18 05:58 PM |
|
|
|
Re: Inventory to DB Script
|
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()
As is talked about in Using Multiple Recordsets
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
...
Thanks,
Kent
|
Lonkero
|
| (KiX Master Guru) |
| 2004-02-18 06:19 PM |
|
Re: Inventory to DB Script
|
eh, why you commented the error check:
Code:
$rs.open($command)
;IF @error<>0
; @error +" and "+ @serror
; GET $k
;ENDIF
as it's crucial.
| Kdyer |
| (KiX Supporter) |
| 2004-02-18 06:23 PM |
|
|
|
Re: Inventory to DB Script
|
Debugging..
| Kdyer |
| (KiX Supporter) |
| 2004-02-18 06:37 PM |
|
|
|
Re: Inventory to DB Script
|
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]
Kent
| Kdyer |
| (KiX Supporter) |
| 2004-02-20 06:23 AM |
|
|
Re: Inventory to DB Script
|
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.
Thanks!
Kent
|
Lonkero
|
| (KiX Master Guru) |
| 2004-02-20 08:30 AM |
|
Re: Inventory to DB Script
|
so, let's do so.
| Radimus |
| (KiX Supporter) |
| 2004-02-20 01:35 PM |
|
|
Re: Inventory to DB Script
|
It would probably be more efficient to have all the WMIQuery calls executed before the recordset/db open statements
| Kdyer |
| (KiX Supporter) |
| 2004-02-20 02:39 PM |
|
|
|
Re: Inventory to DB Script
|
As requested, here is the SQL Server table structure -
Just the tables - Full Drop and build is below
Full version
HTH,
Kent
| Kdyer |
| (KiX Supporter) |
| 2004-02-20 03:00 PM |
|
|
Re: Inventory to DB Script
|
More importantly, it is working now.
** To Do **
Kent
| Kdyer |
| (KiX Supporter) |
| 2004-02-20 05:10 PM |
|
|
|
Re: Inventory to DB Script
|
Stop the presses! There was problem with the previous version of the SQL table generation script. I found this when trying to build a Crystal Report.
Table Creation - Full version is below
Full Version
Thanks,
Kent
| Kdyer |
| (KiX Supporter) |
| 2004-02-22 02:55 AM |
|
|
Re: Inventory to DB Script
|
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.
Kent
| Kdyer |
| (KiX Supporter) |
| 2004-03-05 07:35 AM |
|
|
|
Re: Inventory to DB Script
|
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
| Radimus |
| (KiX Supporter) |
| 2004-03-05 11:37 AM |
|
|
|
Re: Inventory to DB Script
|
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
$note = " " $roomno=" " $phoneno=" " $InsertNewSN=0 $InsertNewModel=0
$Connection = CreateObject("ADODB.Connection")
$Connection.ConnectionString ="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$logon\inventory\inventory.mdb"
$Connection.Open()
if not @error
$Command = CreateObject("ADODB.Command")
$Command.ActiveConnection = $Connection
$Recordset = CreateObject("ADODB.Recordset")
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command
$Command.CommandText = "SELECT * FROM [tbl_Users] WHERE NTAccount='@userid'"
$Recordset.Open($Command)
If $Recordset.RecordCount < 1 $Recordset.AddNew endif
$Recordset.Fields("NTAccount").Value = @USERID
$Recordset.Fields("FirstName").Value = $firstname
$Recordset.Fields("LastName").Value = $lastname
$Recordset.Fields("OrgCode").Value = $OrgCode
$Recordset.Fields("PositionNumber").Value = $positionnum
$Recordset.Fields("PhoneNumber").Value = $phoneno
$Recordset.Fields("RoomNumber").Value = $roomno
$Recordset.Fields("Office").Value = "Area$area"
$Recordset.Update ;if not @error ? " User Inventory updated" else ? @serror endif
$Recordset.Close()
$Command.CommandText = "SELECT * FROM [tbl_Software] WHERE SerialNumber='$SerNo'"
$Recordset.Open($Command)
If $Recordset.RecordCount < 1 $Recordset.AddNew Endif
$Recordset.Fields("SerialNumber").Value = $SerNo
$Recordset.Fields("Windows").Value = @producttype
$Recordset.Fields("ServicePack").Value = @csd
$Recordset.Fields("InternetExplorer").Value = $ier
$Recordset.Fields("Access").Value = $access
$Recordset.Fields("Excel").Value = $excel
$Recordset.Fields("Outlook").Value = $outlook
$Recordset.Fields("Powerpoint").Value = $powerpnt
$Recordset.Fields("Publisher").Value = $pub
$Recordset.Fields("Word").Value = $word
$Recordset.Fields("McAfee").Value = $vscanver
$Recordset.Fields("McAfeeDAT").Value = $vscandat
$Recordset.Fields("Impromptu").Value = $Impver
$Recordset.Fields("Winzip").Value = $Winzipver
$Recordset.Fields("Aspen").Value = $asefiletime
$Recordset.Update ;if not @error ? " Software Inventory updated" else ? @serror endif
$Recordset.Close()
$Command.CommandText = "SELECT * FROM [tbl_Inventory] WHERE SerialNumber='$SerNo'"
$Recordset.Open($Command)
If $Recordset.RecordCount < 1 $Recordset.AddNew $InsertNewSN=1 Endif
$Recordset.Fields("UserName").Value = @userid
$Recordset.Fields("ComputerName").Value = @wksta
$Recordset.Fields("SerialNumber").Value = $SerNo
$Recordset.Fields("IPAddress").Value = $IP
$Recordset.Fields("Memory").Value = $Memory
$Recordset.Fields("LocalPrinter").Value = $Printer
$Recordset.Fields("Modem").Value = $Modem
$Recordset.Fields("InventoryDate").Value = @date
$Recordset.Fields("Location").Value = $Location+', '+$City
$Recordset.Update ;if not @error ? " Logon Inventory updated" else ? @serror endif
$Recordset.Close()
if $InsertNewSN=1
$Command.CommandText = "SELECT * FROM [tbl_Computers] WHERE Model='$Model' and Processor= '$CPUsp'"
$Recordset.Open($Command)
If $Recordset.RecordCount < 1 $InsertNewModel=1 Endif
$Recordset.AddNew
$Recordset.Fields("SerialNumber").Value = $SerNo
$Recordset.Fields("Model").Value = $Model
$Recordset.Fields("Processor").Value = $CPUsp
$Recordset.Fields("AHCATag").Value = $Asset
$Recordset.Fields("OrgCode").Value = $OrgCode
$Recordset.Update ;if not @error ? " Computer Inventory updated" else ? @serror endif
$Recordset.Close()
endif
if $InsertNewModel=1
$Command.CommandText = "SELECT * FROM [tbl_Models] WHERE Model='$Model' and Processor= '$CPUsp'"
$Recordset.Open($Command)
$Recordset.AddNew
$Recordset.Fields("Model").Value = $Model
$Recordset.Fields("Manufacturer").Value = $Make
$Recordset.Fields("Processor").Value = $CPUsp
$Recordset.Fields("EquipmentType").Value = $case
$Recordset.Fields("Video").Value = $video
$Recordset.Fields("HDSize").Value = $hdgb
$Recordset.Update ;if not @error ? " Model Inventory updated" else ? @serror endif
$Recordset.Close()
endif
$Connection.Close()
$Connection = 0
$Recordset = 0
$Command = 0
endif
exit
a blank MDB can be found at www.wheelerfam.com/kixtart/inventory.mdb
| Andreasfcelie |
| (Fresh Scripter) |
| 2004-03-08 10:34 AM |
|
Re: Inventory to DB Script
|
How do I get this to work.
I am running widows Xp, I have tried with the access database, but what do I need, and is it possible to run it locally.
| Kdyer |
| (KiX Supporter) |
| 2004-03-08 03:02 PM |
|
|
|
Re: Inventory to DB Script
|
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.
Kent
| Sealeopard |
| (KiX Master) |
| 2004-03-08 03:46 PM |
|
|
|
Re: Inventory to DB Script
|
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.
| Kdyer |
| (KiX Supporter) |
| 2004-03-08 05:16 PM |
|
|
|
Re: Inventory to DB Script
|
I agree to a point. The point where I am wrestling with this is that the WMIQuery() UDF does not handle wildcards (more than one element at a time for say multiple mapped drives) to extract data needed. Am I missing the point? The DB*() UDFs may cover some simplification of this, we should be able to cover this with say a couple of lines. Anyway, I can ponder this one.
Kent
| Andreasfcelie |
| (Fresh Scripter) |
| 2004-03-09 02:32 PM |
|
Re: Inventory to DB Script
|
I have tried both.
Ik have created an database on the SQL server which I also prefer, I thin k I have troubles with the WMIQuery. I am using windows XP and have tried to use the script locally on my machine where I installed the personal version of SQL Server 2000. I also tried the Access database but also no luck. And how do I use the UDF's? Where to place them, I am not familiar wth this.
Thanx
| Kdyer |
| (KiX Supporter) |
| 2004-03-09 02:51 PM |
|
|
|
Re: Inventory to DB Script
|
First, you want to go to the UDF Forum here on this site and grab the code for WMIQuery and paste it in your script..
BTW, these may come in handy for you..
KiXtart FAQ & How to's
How to use UDFs
Kent
| Andreasfcelie |
| (Fresh Scripter) |
| 2004-03-09 03:09 PM |
|
Re: Inventory to DB Script
|
Is there anyone who had a full working script including all of the UDF etc, please.
Thanx
| Sealeopard |
| (KiX Master) |
| 2004-03-09 04:30 PM |
|
|
|
Re: Inventory to DB Script
|
Full working script for what?
The script in the first post is complete.
I can't be that hard to copy and paste some UDFs into a text file. The FAQ Forum has lots of helpful FAQs for this.
You will also most likely adapt the script code to your particular environment, not something we can help with as do not know your environment.
| tjcarst |
| (Hey THIS is FUN) |
| 2004-03-24 10:47 PM |
|
Re: Inventory to DB Script
|
kdyer
I notice you are calling a script called funct.kix. What is this script doing?
Thanks.
tjcarst
| Sealeopard |
| (KiX Master) |
| 2004-03-24 11:11 PM |
|
|
|
Re: Inventory to DB Script
|
They will most likely include the UDFs he's using throughout the code. See the FAQ Forum for details on UDFs.
| Kdyer |
| (KiX Supporter) |
| 2004-03-25 07:11 AM |
|
|
|
Re: Inventory to DB Script
|
Funct.kix is used to store functions like those of say a VB-Include.
WMIQuery() UDF
You should include the DB*() UDFs instead.
While this version works, the DB*() UDFs will clean up this code a bit more.
Kent
| tjcarst |
| (Hey THIS is FUN) |
| 2004-04-08 05:46 PM |
|
Re: Inventory to DB Script
|
sealeopard - I do realize what UDF's are and use them in my scripts. I do not, however, claim to know enough to write them. Searching the boards for my posts will show my lack of inexperience but also my desire to learn. Please be kind enough to teach me a little so I stop pestering so much. A few words of explanation is all it takes to send me in the right direction.
kdyer - Your patience with those of us not expert in kix is appreciated. Thanks for the follow-up explanation.
tjcarst
| Sealeopard |
| (KiX Master) |
| 2004-04-08 05:56 PM |
|
|
|
Re: Inventory to DB Script
|
Who said anything about writing them? You asked what FUNCT.KIX is and I said that they contain UDFs used in the code. Thus, you'll need to look at the code, identify the UDFs used, copy the UDFs from the UDF Forum into FUNCT.KIX and then run the code.
| tjcarst |
| (Hey THIS is FUN) |
| 2004-04-08 08:23 PM |
|
Re: Inventory to DB Script
|
Thanks for the follow up, sealeopard.
tjcarst
| murk |
| (Lurker) |
| 2004-08-12 09:14 AM |
|
Re: Inventory to DB Script
|
Radimus which UDF of WMIQuery did you use for the software inventory to access DB?
I get the error message like beneath when i use the WMIQuery that is posted.
ERROR : Error in expression: this type of array not supported in expressions.!
Script: Y:\Netlogon\Tools\test.kix
Line : 2
| GeorgeLittle |
| (Fresh Scripter) |
| 2012-10-11 01:49 PM |
|
Re: Inventory to DB Script
|
I have just tried the SQL version of the script and I get an error on line 68 unkown command WMIQuery
|
Lonkero
|
| (KiX Master Guru) |
| 2012-10-11 01:58 PM |
|
Re: Inventory to DB Script
|
lol. scroll back to the top and read the first line of the first post.
|
NTDOC
|
| (KiX Master) |
| 2012-10-12 07:27 AM |
|
|
Re: Inventory to DB Script
|
Well not to mention the code and post is about 8 years old too.
Though I suppose it should still work at the level it was left at.
| Radimus |
| (KiX Supporter) |
| 2012-10-16 04:59 PM |
|
|
|
Re: Inventory to DB Script
|
This is still in use, run as a service on each domain PC
Code:
Break on
$ = SetOption('wrapateol','on')
$ = SetConsole("hide")
$ = RedirectOutput('c:\logfiles\SQLInv.log', 1)
$InvVer = '2012/05/09 - Production Server'
?
? $InvVer
? @DATE + ' '+ @TIME
$start = @ticks
$Make =Trim(Split(WMIQuery("Manufacturer","Win32_ComputerSystem"))[0])
$Model =Trim(WMIQuery("Model","Win32_ComputerSystem"))
$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))
If $serNo < '1' Quit EndIf
$asset =WMIQuery("SMBIOSAssetTag","Win32_SystemEnclosure")
If InStr($asset,"|")
For Each $return in Split($asset,"|")
If $return $asset=$return EndIf
Next
EndIf
$asset = Join(Split($asset,"|"),'')
$asset = Trim(UCase($asset))
$objWMIService = GetObject("winmgmts:\\.\root\cimv2")
if $objWMIService and not @error
$colOperatingSystems = $objWMIService.ExecQuery("Select * From Win32_PerfFormattedData_PerfOS_System")
if $colOperatingSystems and not @error
For Each $objOS in $colOperatingSystems
$uptime = val($objOS.SystemUpTime) / 60 /60
Next
endif
endif
$CPUsp =WMIQuery("CurrentClockSpeed","Win32_Processor")
Select
Case Left($CPUsp,3) = "339" $CPUsp=3400
Case Left($CPUsp,3) = "319" $CPUsp=3200
Case Left($CPUsp,3) = "299" $CPUsp=3000
Case Left($CPUsp,3) = "279" $CPUsp=2800
Case Left($CPUsp,3) = "269" $CPUsp=2600
Case Left($CPUsp,3) = "239" $CPUsp=2400
Case Left($CPUsp,3) = "229" $CPUsp=2200
Case Left($CPUsp,3) = "220" $CPUsp=2200
Case Left($CPUsp,3) = "199" $CPUsp=2000
Case Left($CPUsp,3) = "186" $CPUsp=1860
Case Left($CPUsp,3) = "182" $CPUsp=1830
Case Left($CPUsp,3) = "179" $CPUsp=1800
Case Left($CPUsp,3) = "169" $CPUsp=1700
Case Left($CPUsp,3) = "159" $CPUsp=1600
Case Left($CPUsp,3) = "139" $CPUsp=1400
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
$nic=0
Do
$ip = EnumIPInfo($nic,0)
$snm = EnumIPInfo($nic,1)
$NicDesc= EnumIPInfo($nic,2)
$mygw = EnumIPInfo($nic,3)
$nic=Val($nic)+1
Until InStr($ip,'192.168.') Or InStr($ip,'192.168.24') Or InStr($ip,'192.168.201.') Or Val($nic) > 10
$network = Left($ip,InStrRev($ip,'.'))
$Mac =WMIQuery("MACAddress","Win32_NetworkAdapterConfiguration",,"Description",$NicDesc)
$Mac =Join(Split($mac,':'),'')
$Mac =Join(Split($mac,'|'),'')
If FindProc(@wksta,'MSASCui.exe')
If KeyExists('HKLM\SOFTWARE\Microsoft\Microsoft Forefront\Client Security\1.0\AM\Signature Updates')
$AVProgram = 'Microsoft Forefront Client'
$AVEngine = ReadValue('HKLM\SOFTWARE\Microsoft\Microsoft Forefront\Client Security\1.0\AM\Signature Updates','EngineVersion')
$AVSignature = ReadValue('HKLM\SOFTWARE\Microsoft\Microsoft Forefront\Client Security\1.0\AM\Signature Updates','AVSignatureVersion')
EndIf
EndIf
$MSOdir =ReadValue("HKLM\Software\Microsoft\Windows\CurrentVersion\App Paths\excel.exe","Path")
$excel =GetFileVersion("$MSOdir\excel.exe")
$MSOVer =Split($excel,'.')[0]
$MSOSPVer = ''
$assigned = ReadValue("HKLM\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters","srvcomment")
If Left($assigned,2) = '68'
$assigned = Left(Right(Join(Split(Join(Split($assigned,' '),''),'-'),''),-2),9)
EndIf
$ini = 0
$cnstring = "DRIVER={SQL Server};SERVER=SQLServer;UID=Inventory;PWD=password;DATABASE=InvDB"
$cn = CreateObject("ADODB.Connection")
$cmd= CreateObject("ADODB.Command")
$rs = CreateObject("ADODB.RecordSet")
$cn.connectionstring = $cnstring
$cn.open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmdtxt = "select * from dbo._tbl_Main where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
If $rs.eof = -1 $rs.addnew EndIf
$rs.fields.item("SerialNumber").value = $serNo
$rs.fields.item("ComputerName").value = @wksta
$rs.fields.item("AssignedTo").value = $assigned
$rs.fields.item("NetworkID").value = $network
$rs.fields.item("IPAddress").value = $ip
$rs.fields.item("InvDate").value = @date
$rs.fields.item("Version").value = $InvVer
$rs.fields.item("MacAddr").value = $Mac
$rs.fields.item("Uptime").value = $uptime
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
;if not @error
$nul = WriteValue('HKLM\SOFTWARE\Information Technology\SQLInventory','LastRun',@date,REG_SZ)
;endif
$rs.close
$cmdtxt = "select * from dbo._tbl_OSEnv where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
If $rs.eof = -1 $rs.addnew EndIf
$rs.fields.item("SerialNumber").value = $serNo
$rs.fields.item("OS").value = @ProductType
$rs.fields.item("OSCSD").value = @csd
$rs.fields.item("64bit").value = @OnWoW64
$rs.fields.item("AVProgram").value = $AVProgram
$rs.fields.item("AVEngine").value = $AVEngine
$rs.fields.item("AVSignature").value = $AVSignature
$rs.fields.item("MSO").value = $MSOVer
$rs.fields.item("MSOSP").value = $MSOSPVer
$rs.update ? 'Error = '+@ERROR+' - '+@SERROR
$rs.close
$cmdtxt = "select * from dbo._tbl_OrgCodes where OrgCode = '$Asset'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$code = IIf($rs.eof = -1,1,0)
$rs.close ;? 'Error = '+@ERROR+' - '+@SERROR
$cmdtxt = "select * from dbo._tbl_Computers where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
If $rs.eof = -1
$rs.addnew
$rs.fields.item("SerialNumber").value = $serNo
If $code = 1
$rs.fields.item("Tag").value= $asset
Else
$rs.fields.item("OrgCode").value= $asset
EndIf
$rs.fields.item("Make").value = $Make
$rs.fields.item("Model").value = $Model
$rs.fields.item("CPU").value = $CPUsp
$rs.fields.item("FirstInventory").value = @date
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
;if not @error
$nul = WriteValue('HKLM\SOFTWARE\Information Technology\SQLInventory','FirstRun',@date,REG_SZ)
;endif
Else
If Val($rs.fields.item("Memory").value) <> $memory
$rs.fields.item("Memory").value = Abs(CDbl(Val($memory)))
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
EndIf
If $rs.fields.item("Tag").value < '0' And $code = 1
$rs.fields.item("Tag").value= $asset
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
EndIf
If $rs.fields.item("OrgCode").value < '0' And $code = 0
$rs.fields.item("OrgCode").value= $asset
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
EndIf
If $rs.fields.item("FirstInventory").value < '0'
$rs.fields.item("FirstInventory").value = @date
$rs.update ;? 'Error = '+@ERROR+' - '+@SERROR
EndIf
EndIf
$rs.close
; *********************** Monitor Inventory ***************************************
$cmdtxt = "Delete from dbo._tbl_Monitors where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt ? $cmdtxt
$rs.Open($cmd) ? 'Mon Del-Open: Error = ' + @ERROR + ' - ' + @SERROR
$rs.close ? 'Mon Del-Close: Error = ' + @ERROR + ' - ' + @SERROR
$cmdtxt = "select * from dbo._tbl_Monitors where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$wmiColl = GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor Where ScreenWidth>'0'")
For Each $wmiObj in $wmiColl
$pnpid = $wmiObj.PNPDeviceID
If $pnpid > 'a'
If InStr($pnpid,'\')
$monModel = Split($pnpid,'\')[1]
Else
$monModel = 'Unknown'
EndIf
$res = CStr($wmiObj.ScreenWidth) + "x" + CStr($wmiObj.ScreenHeight)
$rs.addnew
$rs.fields.item("SerialNumber").value = $Serno
$rs.fields.item("MonitorModel").value = $MonModel
$rs.fields.item("Resolution").value = $res
$rs.fields.item("InvDate").value = @date
$rs.update ? 'Mon Add-Update: Error = ' + @ERROR + ' - ' + @SERROR
EndIf
Next
$rs.close ? 'Mon Add-Close: Error = ' + @ERROR + ' - ' + @SERROR
$wmiColl=''
; *********************** Memory Inventory ***************************************
; ******** http://msdn.microsoft.com/en-us/library/aa394347(VS.85).aspx ********
$cmdtxt = "Delete from dbo._tbl_Memory where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt ? $cmdtxt
$rs.Open($cmd) ? 'Mem Del-Open: Error = '+@ERROR+' - '+@SERROR
$rs.close ? 'Mem Del-Close: Error = '+@ERROR+' - '+@SERROR
$cmdtxt = "select * from dbo._tbl_Memory where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt ? $cmdtxt
$rs.open ($cmd) ? 'Mem Add-Open: Error = '+@ERROR+' - '+@SERROR
$wmiColl = GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select * FROM Win32_PhysicalMemory")
For Each $wmiObj in $wmiColl
$rs.addnew
$rs.fields.item("SerialNumber").value = $Serno
$rs.fields.item("DeviceLocator").value = $wmiObj.DeviceLocator
$rs.fields.item("Capacity").value = Abs(CDbl(Val($wmiObj.Capacity))/1024/1024)
$rs.fields.item("Speed").value = $wmiObj.Speed
$rs.fields.item("FormFactor").value = $wmiObj.FormFactor
$rs.fields.item("MemoryType").value = $wmiObj.MemoryType
$rs.fields.item("InvDate").value = @date
$rs.update ? 'Mem Add-Update: Error = ' + @ERROR + ' - ' + @SERROR
Next
$rs.close ? 'Mem Add-Close: Error = '+@ERROR+' - '+@SERROR
$wmiColl=''
; *********************** Disk Drive Inventory ***************************************
; ******** http://msdn.microsoft.com/en-us/library/aa394347(VS.85).aspx ********
$cmdtxt = "Delete from dbo._tbl_DiskDrive where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt ? $cmdtxt
$rs.Open($cmd) ? 'Disk Del-Open: Error = '+@ERROR+' - '+@SERROR
$rs.close ? 'Disk Del-Close: Error = '+@ERROR+' - '+@SERROR
$cmdtxt = "select * from dbo._tbl_DiskDrive where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt ? $cmdtxt
$rs.open ($cmd) ? 'Disk Add-Open: Error = ' + @ERROR + ' - ' + @SERROR
$wmiColl = GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select * FROM Win32_DiskDrive where MediaLoaded='-1'")
For Each $wmiObj in $wmiColl
$rs.addnew
$rs.fields.item("SerialNumber").value = $Serno
$rs.fields.item("DriveSize").value = Round(CDbl($wmiObj.Size)/1000/1000/1000)
$rs.fields.item("InterfaceType").value = $wmiObj.InterfaceType
$rs.fields.item("Model").value = $wmiObj.Model
$rs.fields.item("Status").value = $wmiObj.Status
$rs.fields.item("InvDate").value = @date
$rs.update ? 'Disk Add-Update: Error = ' + @ERROR + ' - ' + @SERROR
Next
$rs.close ? 'Disk Add-Close: Error = ' + @ERROR + ' - ' + @SERROR
; *********************** Software Inventory ***************************************
$cmdtxt = "Delete from dbo._tbl_Software where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt
$rs.Open($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$rs.close ;? 'Error = '+@ERROR+' - '+@SERROR
$cmdtxt = "select * from dbo._tbl_Software where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$Index = 0 $err = 0 $key = "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
Do
$SubKey = EnumKey($Key, $Index)
If @ERROR = 0
If Not Left($subkey,'2') = 'KB'
$DisplayName = ReadValue($key+$subKey,'DisplayName')
$DisplayVersion = ReadValue($key+$subKey,'DisplayVersion')
$InstallDate = ReadValue($key+$subKey,'InstallDate')
$InstallLocation= ReadValue($key+$subKey,'InstallLocation')
$Publisher = ReadValue($key+$subKey,'Publisher')
If Not $DisplayName + $DisplayVersion + $InstallDate + $InstallLocation + $Publisher < 'a'
$rs.addnew
$rs.fields.item("SerialNumber").value = $Serno
$rs.fields.item("RegKeyName").value = $SubKey
$rs.fields.item("DisplayName").value = $DisplayName
$rs.fields.item("DisplayVersion").value = $DisplayVersion
$rs.fields.item("InstallDate").value = $InstallDate
$rs.fields.item("InstallLocation").value = $InstallLocation
$rs.fields.item("Publisher").value = $Publisher
$rs.fields.item("InvDate").value = @date
$rs.update
EndIf
EndIf
$Index = $Index + 1
Else
$err = 1
EndIf
Until $err
$rs.close
; *********************** MSO Sessions Inventory ***************************************
$cmdtxt = "Delete from dbo._tbl_MSOSessions where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt
$rs.Open($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$rs.close ;? 'Error = '+@ERROR+' - '+@SERROR
$cmdtxt = "select * from dbo._tbl_MSOSessions where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open($cmd) ;? 'Error = '+@ERROR+' - '+@SERROR
$getobj = GetObject("winmgmts:{impersonationLevel=impersonate}!//.")
$backup = $getobj.ExecQuery("Select * From Win32_NTLogEvent Where Logfile='osession' and eventcode='7000'") ;and message like 'id: 2%'
For Each $event in $backup
$ec = $event.EventIdentifier
$time = $event.TimeGenerated
$year = SubStr($time, 1, 4) $Hour = SubStr($time, 9, 2)
$month = SubStr($time, 5, 2) $min = SubStr($time, 11, 2)
$day = SubStr($time, 7, 2) $sec = SubStr($time, 13, 2)
$message = $event.message
$user= $event.user
$Msg = Split($message, ",")
$rs.addnew
$rs.fields.item("SerialNumber").value = $Serno
$rs.fields.item("EventID").value = $ec
$rs.fields.item("UserID").value = Join(Split($user,'fdhc\'),'')
$rs.fields.item("Date").value = $year +'/'+ $month +'/'+ $day
$rs.fields.item("Time").value = $hour +':'+ $min +':'+ $sec
$rs.fields.item("ID").value = Trim(Split($msg[0], ':')[1])
$rs.fields.item("AppName").value = Trim(Split($msg[1], ':')[1])
$rs.fields.item("AppVersion").value = Trim(Split($msg[2], ':')[1])
$rs.fields.item("InvDate").value = @date
$rs.update
Next
$rs.close
; *********************** Sprint Card Inventory ***************************************
If Exist('c:\logfiles\sprintcard.txt')
$SprintData = Readfile('c:\logfiles\sprintcard.txt')
For Each $line in $SprintData
Select
Case InStr($line, 'Manufacturer') $man = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Model') $mod = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Revision') $rev = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'ESN') $esn = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Date') $date= Trim(SubStr($line, InStr($line, ':') + 1))
EndSelect
Next
If $esn
$cmdtxt = "select * from dbo._tbl_SprintCards where SerNo = '$SerNo'"
$cmd.commandtext = $cmdtxt
$rs.open($cmd) $err = @serror
If $rs.eof = -1 $rs.addnew EndIf
$rs.fields.item("SerNo").value = $serno
$rs.fields.item("ESN").value = $esn
$rs.fields.item("Manufacturer").value = $man
$rs.fields.item("Model").value = $mod
$rs.fields.item("Revision").value = $rev
$rs.fields.item("InvDate").value = $date
$rs.update $err = @serror
$rs.close
EndIf
EndIf
; *********************** Bitlocker Inventory ***************************************
Shell 'cmd /c echo '+@DATE + '> c:\logfiles\bitlocker.txt'
Shell 'cmd /c manage-bde.exe -status >> c:\logfiles\bitlocker.txt'
Shell 'cmd /c manage-bde.exe -protectors -get c: >> c:\logfiles\bitlocker.txt'
If Exist('c:\logfiles\bitlocker.txt')
$BitData = Readfile('c:\logfiles\bitlocker.txt')
For $count = 0 to UBound($bitdata) - 1
$line = $bitdata[$count]
Select
Case InStr($line, '[OS Volume]') $Vol = Trim(SubStr($bitdata[$count - 1], InStr($bitdata[$count - 1], ':') - 1, 2))
Case InStr($line, 'Bitlocker Version') $Ver = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Conversion Status') $Con = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Encryption Method') $Enc = Trim(SubStr($line, InStr($line, ':') + 1))
Case InStr($line, 'Key Protectors:')
Do
$count = $count +1
If Trim($bitdata[$count]) <> 'Data Recovery Agent (Certificate Based)' And Not InStr($tpm,Trim($bitdata[$count]))
$TPM = $TPM + "," + Trim($bitdata[$count])
EndIf
Until Trim($bitdata[$count + 1]) = ''
$TPM = SubStr($TPM,2)
Case InStr($line, 'External Key file name') $Key = Trim($bitdata[$count + 1])
EndSelect
Next
$data = $vol +', '+$ver + ', ' +$con + ', ' +$enc + ', ' +$tpm + ', ' +$key + ', ' +@DATE
Shell 'cmd /c echo "' + $data + '" >> c:\logfiles\bitlocker.txt'
If $vol
$cmdtxt = "select * from dbo._tbl_bitlocker where SerialNumber = '$SerNo'"
$cmd.commandtext = $cmdtxt
$rs.open($cmd) $err = @serror
Shell 'cmd /c echo open: ' + $err + '>> c:\logfiles\bitlocker.txt'
If $rs.eof = -1 $rs.addnew EndIf
$rs.fields.item("SerialNumber").value = $serno
$rs.fields.item("Volume").value = $vol
$rs.fields.item("Version").value = $ver
$rs.fields.item("Conversion").value = $Con
$rs.fields.item("Encryption").value = $enc
$rs.fields.item("Protection").value = $TPM
$rs.fields.item("Keys").value = $key
$rs.fields.item("InvDate").value = @date
$rs.update $err = @serror
Shell 'cmd /c echo update: ' + $err + '>> c:\logfiles\bitlocker.txt'
$rs.close
EndIf
EndIf
;**************************************************************************************************
Function ReadFile($file)
Dim $lf, $f, $_, $t
$lf = Chr(10)
$f = FreeFileHandle
$_ = Open($f, $file, 2)
If @error Exit 1 EndIf
Do $t = $t + $lf + ReadLine($f) Until @error
$_ = Close($f)
$ReadFile = Split(SubStr($t, 2), $lf)
EndFunction
Function WMIQuery($what,$from,optional $computer,optional $where, optional $x)
Dim $strQuery, $objEnumerator, $value
If Not $computer $computer="@WKSTA" EndIf
$strQuery = "Select $what From $from"
If $where And $x $strQuery = $strQuery + " Where $where = '$x'" EndIf
$SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//$computer")
$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
Function GroupMembers($target, optional $full)
Dim $group, $member, $name, $temp
$group = GetObject('WinNT://' + $target)
If @error Exit 1 EndIf
For Each $member in $group.members
Select
Case $member.class = "user"
$temp = $temp +Chr(13)+ $member.name
Case $member.class = "group"
If $full
For Each $name in $member.members
$temp = $temp +Chr(13)+ $name.name
Next
Else
$temp = $temp +Chr(13)+ $member.name
EndIf
EndSelect
Next
$GroupMembers = Split(SubStr($temp,2),Chr(13))
EndFunction
Function FilterArray($arr_Source, $arr_Filter)
For $item = 0 to UBound($arr_Source)
If Not AScan($arr_Filter,$arr_Source[$item]) = -1 Or Left($arr_Source[$item],5) = 'S-1-5'
$arr_Source[$item] = ''
Else
$arr_Source[$item] = $arr_Source[$item]+ '||'
EndIf
Next
$filterArray = Split(Join($arr_source,''),'||')
EndFunction
;Function looks for a specific running process and returns the PID of it
Function FindPROC($COMPUTER,$PROC,optional $terminate)
Dim $GetObject, $Select
$GetObject="winmgmts:{impersonationLevel=impersonate}!//$COMPUTER"
$select="select * from Win32_Process where Name='$PROC'"
For Each $Process in GetObject("$GetObject").ExecQuery("$select")
If $terminate $Process.Terminate EndIf
$FindPROC=$Process.ProcessId
Next
EndFunction