Hmm possibly I don't think I will be able to accomplish this in the amount of time necessary. The script i downloaded from kixscripts.com in the script archive has the functionality i want but I need to have it collect one or 2 more pieces of data and that is hard enough to figure out because there are fields that i could manuipulate to make it what I want. It is the hardware/software inventory script. It exports to an SQL database which is all working fine. I will post the script and maybe someone can help me troubleshoot it. I would really like it to grab the username of the logged in user and right now it doesn't.
Code:
;Thanks to all of you who have contributed comments or suggestions in the last several days
;while I have been writing this script. You are too many to mention...
;
;Special thanks to scripters whos examples and UDF's I took advantage of to make this come
;together: Lorenz (software Inventory), Radimus (WMIQuery()), ScriptLogic (serialdate())
;
;To use: Create a database in either MSDE or SQL Server then run the following SQL script to
;set up the tables and relationships:
;#### SQL Script
;CREATE TABLE [dbo].[tblCPU] (
; [ID] [int] IDENTITY (1, 1) NOT NULL ,
; [SysID] [int] NULL ,
; [ProcessorID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Manufacturer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Description] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Speed] [int] NULL
;) ON [PRIMARY]
;Go
;
;CREATE TABLE [dbo].[tblComputers] (
; [ID] [int] IDENTITY (1, 1) NOT NULL ,
; [InvDate] [datetime] NULL ,
; [StateNumber] [int] NULL ,
; [SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Manufacturer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [ChassisType] [int] NULL ,
; [Memory] [int] NULL ,
; [OperatingSystem] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
;) ON [PRIMARY]
;Go
;
;CREATE TABLE [dbo].[tblDiskDrive] (
; [ID] [int] IDENTITY (1, 1) NOT NULL ,
; [SysID] [int] NULL ,
; [DriveID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Capacity] [bigint] NULL ,
; [Manufacturer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Model] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
;) ON [PRIMARY]
;Go
;
;CREATE TABLE [dbo].[tblIPConfig] (
; [ID] [int] IDENTITY (1, 1) NOT NULL ,
; [SysID] [int] NULL ,
; [NicID] [int] NULL ,
; [IPAddress] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [SubNetMask] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [MacAddress] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [ServiceName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Description] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
;) ON [PRIMARY]
;Go
;
;CREATE TABLE [dbo].[tblSoftware] (
; [ID] [int] IDENTITY (1, 1) NOT NULL ,
; [SysID] [int] NULL ,
; [Application] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Version] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
; [Publisher] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
;) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblCPU] WITH NOCHECK ADD
; CONSTRAINT [PK_tblCPU] PRIMARY KEY CLUSTERED
; (
; [ID]
; ) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblComputers] WITH NOCHECK ADD
; CONSTRAINT [PK_tblComputers] PRIMARY KEY CLUSTERED
; (
; [ID]
; ) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblDiskDrive] WITH NOCHECK ADD
; CONSTRAINT [PK_tblDiskDrive] PRIMARY KEY CLUSTERED
; (
; [ID]
; ) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblIPConfig] WITH NOCHECK ADD
; CONSTRAINT [PK_tblIPConfig] PRIMARY KEY CLUSTERED
; (
; [ID]
; ) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblSoftware] WITH NOCHECK ADD
; CONSTRAINT [PK_tblSoftware] PRIMARY KEY CLUSTERED
; (
; [ID]
; ) ON [PRIMARY]
;Go
;
;ALTER TABLE [dbo].[tblCPU] ADD
; CONSTRAINT [FK_tblCPU_tblComputers] FOREIGN KEY
; (
; [SysID]
; ) REFERENCES [dbo].[tblComputers] (
; [ID]
; )
;Go
;
;ALTER TABLE [dbo].[tblDiskDrive] ADD
; CONSTRAINT [FK_tblDiskDrive_tblComputers] FOREIGN KEY
; (
; [SysID]
; ) REFERENCES [dbo].[tblComputers] (
; [ID]
; )
;Go
;
;ALTER TABLE [dbo].[tblIPConfig] ADD
; CONSTRAINT [FK_tblIPConfig_tblComputers] FOREIGN KEY
; (
; [SysID]
; ) REFERENCES [dbo].[tblComputers] (
; [ID]
; )
;Go
;
;ALTER TABLE [dbo].[tblSoftware] ADD
; CONSTRAINT [FK_tblSoftware_tblComputers] FOREIGN KEY
; (
; [SysID]
; ) REFERENCES [dbo].[tblComputers] (
; [ID]
; )
;Go
;
;### End SQL Script
;
;Don't forget to modify the connection string for your server and logon information.
;
;NOTE: In our environment we use an environmet variable called "stateno" to record the asset
;nymber of the PC. This portion of the script can be modified to get the asset number out of
;the bois as well. (only if you enter an asset number there in the first place)
;
;I'm sure this is not perfect but it seems to be working for us so far!
;
; !!! Happy Scripting !!! :)
;
;
;================================================================
SetOption("Explicit","ON")
;Declare variables for ADODB Connection and RecordSet
Dim $cn, $rs, $cmd, $cmdtext, $cnstring
;Declare global variables for script
Global $sysid, $WinMgt, $LastInvDate, $RunInterval, $DateDiff, $NextRun, $Item, $i, $iValue
;Database connection string
$cnstring = "Driver={SQL Server};Server=Sussex-SQL;database=Inventory;uid=inventory;pwd=inventory"
;Creating Connection, Command and Recordset objects
$cn = CreateObject("adodb.connection")
$cmd = CreateObject("adodb.command")
$rs = CreateObject("adodb.recordset")
;Open Database Conection
$cn.connectionstring = $cnstring
$cn.Open
;Set command and recordset properties
$cmd.activeconnection = $cn
$rs.cursortype = 1
$rs.locktype = 3
$rs.activecommand = $cmd
;*****************************************************************************
; For some reason had to comment out this section or the script would not run.
; Found this fix on www.kixscripts.com
;*****************************************************************************
;Check Run Settings
;If KeyExist("Hkey_Local_Machine\Software\Inventory")
; $LastInvDate = ReadValue("Hkey_Local_Machine\Software\Inventory","InventoryRunDate")
; $RunInterval = ReadValue("Hkey_Local_Machine\Software\Inventory","InventoryRunInterval")
;Else
; $LastInvDate = @DATE
; $RunInterval = "0"
;EndIf
$DateDiff = fnSerialDate(@DATE) - fnSerialDate($LastInvDate)
If $DateDiff < $RunInterval
Exit
EndIf
?"Your IT Department is conducting an inventory of your PC."
?"Please wait as the information is collected."
SRnd(@TICKS)
$NextRun = Rnd()
$NextRun = Rnd(23)+7
;WriteValue("Hkey_Local_Machine\Software\Inventory","InventoryRunDate",@DATE,"REG_SZ")
;WriteValue("Hkey_Local_Machine\Software\Inventory","InventoryRunInterval",$NextRun,"REG_SZ")
;Begin Inventory Collection
;Create WMI Object
$WinMgt = GetObject("winmgmts:")
$cmdtext = "Select * from tblComputers where name = '@WKSTA'"
$cmd.commandtext = $cmdtext
$rs.Open($cmd)
If $rs.eof = -1
$rs.addnew
EndIf
Dim $CompSys, $OpSys, $Bios, $Environment, $Drives, $CPU, $Chassis, $IP
;Update / Insert tblComputers
?"Base System Information"
$CompSys = $WinMgt.ExecQuery("Select * from Win32_ComputerSystem")
For Each $item In $CompSys
$rs.fields.item("Name").Value = $item.Name
$rs.fields.item("Manufacturer").value = $item.manufacturer
$rs.fields.item("Model").value = $item.model
$rs.fields.item("Memory").value = $item.TotalPhysicalMemory
Next
$OpSys = $WinMgt.ExecQuery("Select Caption from Win32_OperatingSystem")
For Each $item In $OpSys
$rs.fields.item("OperatingSystem").value = $item.Caption
Next
$Bios = $WinMgt.ExecQuery("Select SerialNumber from Win32_bios")
For Each $item In $bios
$rs.fields.item("SerialNumber").value = $item.serialnumber
Next
$Environment = $WinMgt.Execquery("Select VariableValue from win32_environment where name = 'stateno'")
For Each $item In $environment
$rs.fields.item("StateNumber").value = $item.VariableValue
Next
$Chassis = $WinMgt.ExecQuery("Select ChassisTypes from Win32_SystemEnclosure where tag = 'System Enclosure 0'")
For Each $item In $Chassis
$rs.fields.item("ChassisType").value = $item.ChassisTypes(0)
Next
$rs.fields.item("InvDate").Value = @DATE + " " + @TIME
$rs.update
$SysID = $rs.fields.item("ID").value
$rs.Close
;Update / Insert tblCPU
?"CPU(s)"
$cmdtext = "Select * from tblCPU where sysid = '" + $SysID + "'"
$cmd.commandtext = $cmdtext
$rs.Open($cmd)
If $rs.eof <> -1
$rs.movefirst
While $rs.eof <> -1
$rs.delete
$rs.movenext
Loop
$rs.update
EndIf
$CPU = $WinMgt.ExecQuery("Select * from win32_processor")
For Each $item In $CPU
$rs.addnew
$rs.fields.item("sysid").value = $SysID
$rs.fields.item("ProcessorID").value = $Item.DeviceID
$rs.fields.item("Manufacturer").value = $Item.Manufacturer
$rs.fields.item("Description").value = $Item.Description
$rs.fields.item("Speed").value = $Item.MaxclockSpeed
Next
$rs.update
$rs.Close
;Update / Insert tbldiskDrive
?"Hard Disk Drive(s)"
$cmdtext = "Select * from tblDiskDrive where sysid = '" + $SysID + "'"
$cmd.commandtext = $cmdtext
$rs.Open($cmd)
If $rs.eof <> -1
$rs.movefirst
While $rs.eof <> -1
$rs.delete
$rs.movenext
Loop
$rs.update
EndIf
$Drives = $WinMgt.ExecQuery("Select * From Win32_DiskDrive")
For Each $Item In $Drives
$rs.addNew
$rs.fields.item("sysid").value = $SysID
$rs.fields.item("DriveID").value = $item.DeviceID
$rs.fields.item("Capacity").value = $Item.size
$rs.fields.item("Manufacturer").value = $Item.Manufacturer
$rs.fields.item("Model").Value = $item.model
Next
$rs.update
$rs.Close
;Update / Insert IPConfig
?"Network Adapter(s) Configuration"
$cmdtext = "Select * from tblipconfig where sysid = '" + $SysID + "'"
$cmd.commandtext = $cmdtext
$rs.Open($cmd)
If $rs.eof <> -1
$rs.movefirst
While $rs.eof <> -1
$rs.delete
$rs.movenext
Loop
$rs.update
EndIf
$IP = $WinMgt.ExecQuery("Select * from Win32_NetworkAdapterConfiguration where IPEnabled = true")
For Each $Item In $IP
$iValue = $Item.IPAddress
For $i = 0 to Ubound($ivalue)
$rs.addnew
$rs.fields.item("sysid").value = $sysid
$rs.fields.item("nicid").value = $i
$rs.fields.item("ipaddress").value = $item.IPAddress($i)
$rs.fields.item("SubNetMask").value = $item.ipsubnet($i)
$rs.fields.item("MacAddress").value = $item.MACAddress
$rs.fields.item("ServiceName").Value = $item.ServiceName
$rs.fields.item("description").value = $item.description
Next
Next
$rs.Update
$rs.Close
;Update / Insert tblSoftware
?"Installed Software"
Dim $Index, $KeyName, $Application, $Version, $Publisher
$cmdtext = "Select * from tblsoftware where sysid = '" + $SysID + "'"
$cmd.commandtext = $cmdtext
$rs.Open($cmd)
If $rs.eof <> -1
$rs.movefirst
While $rs.eof <> -1
$rs.delete
$rs.movenext
Loop
$rs.update
EndIf
$Index = 0
$KeyName = EnumKey("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall", $Index)
$Application = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayName")
$Version = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayVersion")
$Publisher = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "Publisher")
While Len($keyname) > 0
If Len($Application)>0
$rs.addnew
$rs.fields.item("SysID").value = $SysID
$rs.fields.item("Application").value = $Application
$rs.fields.item("Version").value = $Version
$rs.fields.item("Publisher").value = $publisher
EndIf
$Index = 1 + $Index
$KeyName = EnumKey("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall", $Index)
$Application = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayName")
$Version = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "DisplayVersion")
$Publisher = ReadValue("HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall\$KeyName", "Publisher")
Loop
$rs.update
$rs.Close
?"Inventory Complete!"
?"Thank You!"
Sleep 2
Function fnSerialDate($ExpD)
Dim $z,$h,$a,$b,$c,$y,$m,$d
If InStr($ExpD,'/')
$ExpD=Split($ExpD,'/')
$y=Val($ExpD[0])
$m=Val($ExpD[1])
$d=Val($ExpD[2])
If $m<3
$m=$m+12
$y=$y-1
EndIf
$fnSerialDate=$d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306
Else
$z=0+$ExpD+306
$h=100*$z-25
$a=$h/3652425
$b=$a-$a/4
$y=(100*$b+$h)/36525
$c=$b+$z-365*$y-$y/4
$m=(5*$c+456)/153
$d=$c-(153*$m-457)/5
If $m>12
$y=$y+1
$m=$m-12
EndIf
$fnSerialDate=Right('0000'+$y,4)+'/'+Right('00'+$m,2)+'/'+Right('00'+$d,2)
EndIf
EndFunction
