Ori
Lurker
Registered: 2004-07-28
Posts: 1
|
OK.
Here we go again.
I have downloaded a software/hardware inventory kix script from the resources. I have tried everything to get it to work for the last week but am failing badly.
Below is the code followed by the tables to be created in SQL.
I have created all the tables perfectly but when the kix script is run, the gathered information does not get entered into the SQL database but completes without error. I have turned on debug and no errors appear.
Has anyone any ideas about this as this script would be great if it worked. I do not expect something for nothing, as I have tried various options and resources to get it fixed.
Help I am going bald.
------------------SCRIPT-------------------
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=MyServer;database=MyDatabase;uid=UserName;pwd=password"
;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
;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
-------------------END-----------------------
--------SQL Script to create Tables----------
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------------------------
|
