#114213 - 2004-02-20 06:23 AM
Re: Inventory to DB Script
Kdyer
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. ;DB Inventory Script - Initially built by Neil Moran ;Modified by Kent Dyeer 19-February-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 $x =SETOPTION ('WrapAtEOL' ,'On' ) $rc =SETOPTION ('Explicit' ,'On' ) $rc =SETOPTION ('NoVarsInStrings' ,'On' ) $rc =SETOPTION ('CaseSensitivity' ,'On' ) $wmi = 'WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONATE}!//@WKSTA' $dsn ='Driver={SQL Server};' $dsn =$dsn +'Server=SQLSERVERNAME;' $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 =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 $rs.close $rs.open ($command ) ;ID the Computer $pcidentifier =$rs.fields ('PCID' ).value $rs.close ;Check for multiple disks FOR EACH $disk IN GetObject ($wmi ).execquery ('SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=3' ) ;Open the Disk RecordSet $command.commandtext ="SELECT * FROM TBL_DISKS WHERE COMPID=" +$pcidentifier +" AND DRIVENAME='" +$disk.name +"';" $rs.open ($command ) IF $rs.recordcount < 1 $rs.addnew ENDIF $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 $rs.update $rs.close NEXT ;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 $command.commandtext ="SELECT * FROM TBL_SHARES WHERE COMPID=" +$pcidentifier +" AND SHARENAME='" +$share.name +"'" $rs.open ($command ) IF $rs.recordcount < 1 $rs.addnew 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 $rs.update $rs.close NEXT ;And now grab Network Adapter info and update specific table FOR EACH $card IN GetObject ($wmi ).execquery ("SELECT * FROM WIN32_NETWORKADAPTERCONFIGURATION WHERE IPENABLED='TRUE'" ) $command.commandtext ="SELECT * FROM TBL_NETWORKADAPTERS WHERE COMPID=" +$pcidentifier +" AND DEVICEID=" +$card.index +";" $rs.open ($command ) IF $card.dhcpenabled $dhcp ='DHCP Enabled' ELSE $dhcp ='Static address' ENDIF IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields ('compID' ).value =$pcidentifier $rs.fields ('DeviceID' ).value =$card.index $rs.fields ('NetCard' ).value =$card.description $rs.fields ('IPAddress' ).value =join (split (@ipaddress0 ,' ' ),'' ) $rs.fields ('MACAddress' ).value =@address $rs.fields ('DHCP' ).value =$dhcp $rs.update $rs.close NEXT ;Collect Printer Connection info - should grab local as well FOR EACH $printer IN GetObject ($wmi ).execquery ('SELECT * FROM WIN32_PRINTER' ) $command.commandtext ="SELECT * FROM TBL_PRINTERCONNECTIONS WHERE COMPID=" +$pcidentifier +" AND PRINTERID='" +$printer.deviceid +"';" $rs.open ($command ) IF $rs.recordcount < 1 $rs.addnew ENDIF $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 $rs.update $rs.close NEXT FOR EACH $drive IN GetObject ($wmi ).execquery ('SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=4' ) $command.commandtext ="SELECT * FROM TBL_MAPPEDDRIVES WHERE USERID=" +$useridentifier +" AND LETTER='" +$drive.name +"';" $rs.open ($command ) IF $rs.recordcount < 1 $rs.addnew ENDIF $rs.fields ('UserID' ).value =$useridentifier $rs.fields ('Letter' ).value =$drive.name $rs.fields ('Path' ).value =$drive.providername $rs.update $rs.close () NEXT ;Close all $connection.close () ;Flush all $connection = 0 $rs = 0 $command = 0 If needed, I can post the construct for the tables for SQL Server. Thanks! Kent
Top
Moderator: Glenn Barnas , NTDOC , Arend_ , Jochen , Radimus , Allen , ShaneEP , Ruud van Velsen , Mart
0 registered
and 262 anonymous users online.