;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