Page 1 of 2 12>
Topic Options
#114205 - 2004-02-17 05:55 PM Inventory to DB Script
Kdyer Offline
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')

$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

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

Top
#114206 - 2004-02-18 02:54 AM Re: Inventory to DB Script
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
eh, did I get it...
instead of update, it adds new ones?
sounds like it ain't closing or something...
_________________________
!

download KiXnet

Top
#114207 - 2004-02-18 02:26 PM Re: Inventory to DB Script
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
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.

;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
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#114208 - 2004-02-18 04:27 PM Re: Inventory to DB Script
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
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.
_________________________
There are two types of vessels, submarines and targets.

Top
#114209 - 2004-02-18 05:58 PM Re: Inventory to DB Script
Kdyer Offline
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()





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
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#114210 - 2004-02-18 06:19 PM Re: Inventory to DB Script
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
eh, why you commented the error check:
Code:

$rs.open($command)
;IF @error<>0
; @error +" and "+ @serror
; GET $k
;ENDIF



as it's crucial.
_________________________
!

download KiXnet

Top
#114211 - 2004-02-18 06:23 PM Re: Inventory to DB Script
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Debugging..
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#114212 - 2004-02-18 06:37 PM Re: Inventory to DB Script
Kdyer Offline
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]





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

Top
#114213 - 2004-02-20 06:23 AM Re: Inventory to DB Script
Kdyer Offline
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
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#114214 - 2004-02-20 08:30 AM Re: Inventory to DB Script
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
so, let's do so.
_________________________
!

download KiXnet

Top
#114215 - 2004-02-20 01:35 PM Re: Inventory to DB Script
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
It would probably be more efficient to have all the WMIQuery calls executed before the recordset/db open statements
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#114216 - 2004-02-20 02:39 PM Re: Inventory to DB Script
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
As requested, here is the SQL Server table structure -

Just the tables - Full Drop and build is below
USE logins
/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/13/2004 3:04:54 PM ******/
CREATE TABLE [dbo].[tbl_Computers] (
[PCID] [int] IDENTITY (1, 1) NOT NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemManufacturer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemModel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServicePack] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhysicalMemory] [int] NULL ,
[ProcessorSpeed] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PagefileSpace] [float] NULL ,
[DiskController] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoggedOnUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_Disks] (
[compID] [int] NULL ,
[DriveName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreeSpace] [float] NULL ,
[TotalSpace] [float] NULL ,
[Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_MappedDrives] (
[UserID] [int] NOT NULL ,
[Letter] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_NetworkAdapters] (
[compID] [int] IDENTITY (1, 1) NOT NULL ,
[DeviceID] [int] NULL ,
[NetCard] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MACAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DHCP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_PrinterConnections] (
[compID] [int] NOT NULL ,
[PrinterID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PortName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/13/2004 3:04:56 PM ******/
CREATE TABLE [dbo].[tbl_Shares] (
[compID] [int] NOT NULL ,
[ShareName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SharePath] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShareType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hyperlink] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/13/2004 3:04:56 PM ******/
CREATE TABLE [dbo].[tbl_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsLocalAdmin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrivilegeLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeDrive] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



Full version

USE logins
/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Computers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Computers]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Disks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Disks]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_MappedDrives]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_MappedDrives]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_NetworkAdapters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_NetworkAdapters]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_PrinterConnections]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_PrinterConnections]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Shares]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Shares]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/13/2004 3:04:51 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Users]
GO

/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/13/2004 3:04:54 PM ******/
CREATE TABLE [dbo].[tbl_Computers] (
[PCID] [int] IDENTITY (1, 1) NOT NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemManufacturer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemModel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServicePack] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhysicalMemory] [int] NULL ,
[ProcessorSpeed] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PagefileSpace] [float] NULL ,
[DiskController] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoggedOnUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_Disks] (
[compID] [int] NULL ,
[DriveName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreeSpace] [float] NULL ,
[TotalSpace] [float] NULL ,
[Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_MappedDrives] (
[UserID] [int] NOT NULL ,
[Letter] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_NetworkAdapters] (
[compID] [int] IDENTITY (1, 1) NOT NULL ,
[DeviceID] [int] NULL ,
[NetCard] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MACAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DHCP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/13/2004 3:04:55 PM ******/
CREATE TABLE [dbo].[tbl_PrinterConnections] (
[compID] [int] NOT NULL ,
[PrinterID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PortName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/13/2004 3:04:56 PM ******/
CREATE TABLE [dbo].[tbl_Shares] (
[compID] [int] NOT NULL ,
[ShareName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SharePath] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShareType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hyperlink] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/13/2004 3:04:56 PM ******/
CREATE TABLE [dbo].[tbl_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsLocalAdmin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrivilegeLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeDrive] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


HTH,

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

Top
#114217 - 2004-02-20 03:00 PM Re: Inventory to DB Script
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
More importantly, it is working now.

** To Do **

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

Top
#114218 - 2004-02-20 05:10 PM Re: Inventory to DB Script
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
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
USE LOGINS
/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/20/2004 7:46:28 AM ******/
CREATE TABLE [dbo].[tbl_Computers] (
[PCID] [int] IDENTITY (1, 1) NOT NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemManufacturer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemModel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServicePack] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhysicalMemory] [int] NULL ,
[ProcessorSpeed] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PagefileSpace] [float] NULL ,
[DiskController] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoggedOnUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/20/2004 7:46:28 AM ******/
CREATE TABLE [dbo].[tbl_Disks] (
[compID] [int] NULL ,
[DriveName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreeSpace] [float] NULL ,
[TotalSpace] [float] NULL ,
[Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_MappedDrives] (
[UserID] [int] NOT NULL ,
[Letter] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_NetworkAdapters] (
[compID] [int] IDENTITY (1, 1) NOT NULL ,
[DeviceID] [int] NULL ,
[NetCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MACAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DHCP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_PrinterConnections] (
[compID] [int] NOT NULL ,
[PrinterID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PortName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_Shares] (
[compID] [int] NOT NULL ,
[ShareName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SharePath] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShareType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hyperlink] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsLocalAdmin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrivilegeLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeDrive] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Full Version
USE LOGINS
/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Computers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Computers]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Disks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Disks]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_MappedDrives]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_MappedDrives]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_NetworkAdapters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_NetworkAdapters]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_PrinterConnections]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_PrinterConnections]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Shares]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Shares]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/20/2004 7:46:26 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Users]
GO

/****** Object: Table [dbo].[tbl_Computers] Script Date: 2/20/2004 7:46:28 AM ******/
CREATE TABLE [dbo].[tbl_Computers] (
[PCID] [int] IDENTITY (1, 1) NOT NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemManufacturer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemModel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServicePack] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhysicalMemory] [int] NULL ,
[ProcessorSpeed] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PagefileSpace] [float] NULL ,
[DiskController] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VidCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoggedOnUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Disks] Script Date: 2/20/2004 7:46:28 AM ******/
CREATE TABLE [dbo].[tbl_Disks] (
[compID] [int] NULL ,
[DriveName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreeSpace] [float] NULL ,
[TotalSpace] [float] NULL ,
[Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_MappedDrives] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_MappedDrives] (
[UserID] [int] NOT NULL ,
[Letter] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_NetworkAdapters] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_NetworkAdapters] (
[compID] [int] IDENTITY (1, 1) NOT NULL ,
[DeviceID] [int] NULL ,
[NetCard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MACAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DHCP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_PrinterConnections] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_PrinterConnections] (
[compID] [int] NOT NULL ,
[PrinterID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PortName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Shares] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_Shares] (
[compID] [int] NOT NULL ,
[ShareName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SharePath] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShareType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hyperlink] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[tbl_Users] Script Date: 2/20/2004 7:46:29 AM ******/
CREATE TABLE [dbo].[tbl_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsLocalAdmin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrivilegeLevel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeDrive] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Thanks,

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

Top
#114219 - 2004-02-22 02:55 AM Re: Inventory to DB Script
Kdyer Offline
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.

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

Top
#114220 - 2004-03-05 07:35 AM Re: Inventory to DB Script
Kdyer Offline
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

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

Top
#114221 - 2004-03-05 11:37 AM Re: Inventory to DB Script
Radimus Moderator Offline
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


$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
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#114222 - 2004-03-08 10:34 AM Re: Inventory to DB Script
Andreasfcelie Offline
Fresh Scripter

Registered: 2004-03-08
Posts: 6
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.

Top
#114223 - 2004-03-08 03:02 PM Re: Inventory to DB Script
Kdyer Offline
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.

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

Top
#114224 - 2004-03-08 03:46 PM Re: Inventory to DB Script
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
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.

Top
Page 1 of 2 12>


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 466 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.076 seconds in which 0.027 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org