Does Anyone Have A Good Working Asset Management S - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Lounge » Does Anyone Have A Good Working Asset Management S

Page 1 of 2

Topic Options

#109524 - 2003-12-03 08:01 PM Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	I would like to add to my existing login script and run a script that takes a decent inventory of the system; hardware, software, username, ip address, workstation name, etc. And take that data and dump it into an SQL database. I have researched several forums on the net and haven't found anything that people say works. I see lots of samples of code and after it is posted people say it doesn't work. Does anyone have any good asset management script other than dumping the data to a text file? Thanks in advance.
Top

#109525 - 2003-12-03 09:21 PM

Re: Does Anyone Have A Good Working Asset Management S

Jose

Seasoned Scripter

Registered: 2001-04-04
Posts: 693
Loc: Buenos Aires - Argentina

DJ:
I dont think that there is an accurate script to be posted to achieve your goal, I think you will have to build it yourserl.
Some of the things you have asked for are simple macros wich you can find in the manual (@USERID fe)....take a look at them. Others you will need UDF´s, for hardware inventory you can start with WMIQuery(), not suported in old mothers. For software inventory you can start with this function wich I coudn find so I post it here:
Code:


Function GetUninstallInfo()
 Dim $Index, $Key, $RC, $Value, $RootKey
 Dim $progs[0]
 $Index = 0
 $RootKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
 $Key = EnumKey($RootKey, $Index)
 While @Error = 0
   ReDim PRESERVE $progs[$Index]
   $RC = EnumValue($RootKey + $Key, 1)
   If @Error = 0
     $Value = ReadValue($RootKey + $Key, "DisplayName")
     If $Value = ""
      $Value = ReadValue($RootKey + $Key, "QuietDisplayName")
      If $Value = ""
       $Value = $Key
      EndIf
     EndIf
    Else
     $Value = $Key
    EndIf
    $progs[$Index] = $Value
    $Index = $Index + 1
    $Key = EnumKey($RootKey, $Index)
   Loop
   $GetUninstallInfo = $progs
EndFunction

To use this soft UDF you will have to paste it somewhere in your code and call it like this $array = GetUninstallInfo() so $array will hold all the soft installed.

To obtain ip and stuff start with EnumIPInfoAll() and to dump all data to a database you will have to reorgize the data before using DBConnOpen() , DBCommand() , DBConnClose().

In almoust all the cases you will need to have some knoledge in managing data (arrays in script) and database. As allways there should be several ways of doing this but this the one I use, maybe is the tuffest but you can learn more.
Hope this can be helpfull.

_________________________
Life is fine.

Top

#109526 - 2003-12-03 10:34 PM Re: Does Anyone Have A Good Working Asset Management S
Radimus Radimus Moderator Registered: 2000-01-06 Posts: 5187 Loc: Tampa, FL	i've updated the access MDB and the inventory.kix at my web site. not the inventory code doesn't collect all the data, it only contains some sample WMIQuery() statements and the code to populate the access tables _________________________ How to ask questions the smart way <-----------> Before you ask
Top

#109527 - 2003-12-04 02:37 PM

Re: Does Anyone Have A Good Working Asset Management S

DJ Ballistic Offline

Starting to like KiXtart

Registered: 2003-02-21
Posts: 185

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

Top

#109528 - 2003-12-04 02:58 PM Re: Does Anyone Have A Good Working Asset Management S
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	DJ, I think this maybe helpful for others too.. OK.. Let's break this down.. It seems that you have created the needed tables in SQL. However, when you run the script, it does not update or change any data in the tables? Have you tried to debug the script at all? We did some work in the thread: Re- Inventory Management Anyway, when I get into work.. I can post it for you as it works great.. It looks like something maybe up with your dbconnection. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#109529 - 2003-12-04 03:20 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Ok first. I created all of the necessary tables in SQL. The connection works great as well. I ran the kixscript on my local machine and it did fill in the data into the database nicely. My only problem is that i would like to see a couple of more pieces of data in place of what is there. For instance it has StateNumber which shows up as NULL and I don't use it anyway. I would love for it to show the currently logged in userID. The full name from AD would also be nice. I am almost there to exactly what I need. There is enough other things getting pulled that I can use. Thank you for helping.
Top

#109530 - 2003-12-04 03:41 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	I also notice this script takes the information from the user running it so it isn't something that I can run as an IT administration tool to go search the domain and collect the info and dump it to the sql database so I am going to have to add it to my existing logon script and have it do a check to see if it has already run so it doesn't run everytime or something, i dunno. Not sure bout what to do I am still pretty new to this kixscript thing. I found this working script in the script library of kixscripts.com.
Top

#109531 - 2003-12-04 06:19 PM

Re: Does Anyone Have A Good Working Asset Management S

Kdyer

KiX Supporter

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR

DJ,

Here is the script..
Code:


cls
break on
;Script to write info to database at logon - uses WMI and ADO for connection and collection
;Created Feb 2002 by Neil Moran

;First, set variables for the connection to the database and other general options
$x=SetOption("WrapAtEOL","On")
;$DATABASE = "\\server\share\logon.mdb"
;$DSN = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
;http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer
$DSN="Driver={SQL Server};"
$DSN=$DSN+"Server=SQLSERVER;"
$DSN=$DSN+"Database=Logins;"
$DSN=$DSN+"Uid=;"
$DSN=$DSN+"Pwd="

$WMI = "WINMGMTS:{IMPERSONATIONLEVEL=IMPERSONATE}!//@WKSTA"
$WMI_Test = GetObject($WMI)
If @ERROR <> 0
	Goto WMI_Error
Endif
$WMI_Test = 0

;Create SQL statements to check/write/update database
$SQL_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';"
$SQL_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';"

;Create database objects
$Connection = CreateObject("ADODB.Connection")
;? "Create Connection object " + @ERROR + ": " + @SERROR Get $x
$Command = CreateObject("ADODB.Command")
;? "Create Command object " + @ERROR + ": " + @SERROR Get $x
$Recordset = CreateObject("ADODB.Recordset")
;? "Create recordset object " + @ERROR + ": " + @SERROR Get $x
	
;Set properties of DB objects and open connection to database
$Connection.ConnectionString = $DSN
$Connection.Open()
;? "Open connection " + @ERROR + ": " + @SERROR Get $x
If @ERROR <> 0
	Goto ADO_Error
Endif
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command

;Check for User details first
$Command.CommandText = $SQL_USERS
$Recordset.Open($Command)
;? "Open user recordset " + @ERROR + ": " + @SERROR Get $x
	
;Create new record if none exists to update
If $Recordset.RecordCount < 1
	$Recordset.AddNew
	;? "Add new user record " + @ERROR + ": " + @SERROR Get $x
Endif

If InGroup("\\" + @WKSTA + "\Administrators")
	$IsLocalAdmin = "True"
Else
	$IsLocalAdmin = "False"
Endif

;Write user values into database field by field
$Recordset.Fields("UserName").Value = @USERID
$Recordset.Fields("FullName").Value = @FULLNAME
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("IsLocalAdmin").Value = $IsLocalAdmin
$Recordset.Fields("PrivilegeLevel").Value = @PRIV
$Recordset.Fields("HomeDrive").Value = @HOMESHR
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME
$Recordset.Fields("Description").Value = @COMMENT

;Update the new record and close the recordset object
$Recordset.Update
;? "Update users " + @ERROR + ": " + @SERROR Get $x
$UserIdentifier = $Recordset.Fields("UserID").Value
?$UserIdentifier
$Recordset.Close()
;? "Close user recordset " + @ERROR + ": " + @SERROR Get $x

;Check Computer details next - refresh $Recordset object with new query
$Command.CommandText = $SQL_COMPUTERS
$Recordset.Open($Command)
;? "Open computer recordset " + @ERROR + ": " + @SERROR Get $x

;Check for existing record to update
If $Recordset.RecordCount < 1
	$Recordset.AddNew
	;? "Add new computer record " + @ERROR + ": " + @SERROR Get $x
Endif

$SystemManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SystemModel = WMIQuery("Model","Win32_ComputerSystem")
$SerialNo = WMIQuery("SerialNumber","Win32_BIOS")
$PhysicalMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
$ProcessorSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
$PagefileSpace = val(WMIQuery("TotalPageFileSpace","Win32_LogicalMemoryConfiguration"))/1024
$VidMode = WMIQuery("VideoModeDescription","Win32_VideoController")
$VidCard = WMIQuery("VideoProcessor","Win32_VideoController")
If Len($VidCard) = 0
	$VidCard = WMIQuery("Description","Win32_VideoController")
Endif
$DiskController = WMIQuery("InterfaceType","Win32_DiskDrive")
$Modem = WMIQuery("Description","Win32_POTSModem")

;Write values into table
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("NTDomain").Value = @DOMAIN
$Recordset.Fields("SystemManufacturer").Value = $SystemManufacturer
$Recordset.Fields("SystemModel").Value = $SystemModel
$Recordset.Fields("SerialNo").Value = $SerialNo
$Recordset.Fields("OS").Value = @PRODUCTTYPE
$Recordset.Fields("ServicePack").Value = @CSD
$Recordset.Fields("PhysicalMemory").Value = $PhysicalMemory
$Recordset.Fields("ProcessorSpeed").Value = $ProcessorSpeed
$Recordset.Fields("PagefileSpace").Value = $PagefileSpace
$Recordset.Fields("VidMode").Value = $VidMode
$Recordset.Fields("VidCard").Value = $VidCard
$Recordset.Fields("Modem").Value = $Modem
$Recordset.Fields("LoggedOnUser").Value = @USERID
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME

;Update and close recordset object
$Recordset.Update
;? "Update computer record " + @ERROR + ": " + @SERROR Get $x
$PCIdentifier = $Recordset.Fields("PCID").Value
;? "PC ID number is " + $PCIdentifier
IF $PCIdentifier<>""
$Recordset.Close()
ENDIF
;? "Close computer recordset " + @ERROR + ": " + @SERROR Get $x

;Slightly different process for update of Disks table
$DiskSet = GetObject($WMI).ExecQuery("SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=3")
;? "Get DiskSet info from WMI " + @ERROR + ": " + @SERROR Get $x
For Each $Disk In $DiskSet
	$Name = $Disk.Name
	;? "Disk name is " + $Name
	$FreeSpace = $Disk.FreeSpace
	;? "Free space is " + $FreeSpace
	$TotalSpace = $Disk.Size
	;? "Total size is " + $TotalSpace
	$Format = $Disk.FileSystem
	;? "Disk format is " + $Format
	$SQL_DISKS = 'SELECT * FROM TBL_DISKS WHERE COMPID='+$PCIDENTIFIER+' AND DRIVENAME=' + $NAME + ';'
	$Command.CommandText = $SQL_DISKS
	$Recordset.Open($Command)
	;? "Open disks recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
		;? "Add new disk record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("DriveName").Value = $Name
	$Recordset.Fields("FreeSpace").Value = $FreeSpace
	$Recordset.Fields("TotalSpace").Value = $TotalSpace
	$Recordset.Fields("Format").Value = $Format
	$Recordset.Update
	;? "Update disk record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close disk recordset " + @ERROR + ": " + @SERROR Get $x
Next

;Collect share info on the local machine
$WMI_SHARE = "SELECT * FROM WIN32_SHARE"
$Shares = GetObject($WMI).ExecQuery($WMI_SHARE)
;? "Get share info from WMI " +  @ERROR + ": " + @SERROR Get $x
For Each $Share In $Shares
	$Name = $Share.Name
	$Path = $Share.Path
	$Desc = $Share.Description
	$Type = $Share.Type
	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
	$Hyperlink = "\\" + @WKSTA + "\" + $Name
	$SQL_SHARES = "SELECT * FROM TBL_SHARES WHERE COMPID=" + $PCIDENTIFIER + " AND SHARENAME='" + $NAME + "'"
	$Command.CommandText = $SQL_SHARES
	$Recordset.Open($Command)
	;? "Open Share recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
		;? "Add new share record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("ShareName").Value = $Name
	$Recordset.Fields("SharePath").Value = $Path
	$Recordset.Fields("Description").Value = $Desc
	$Recordset.Fields("ShareType").Value = $Type
	$Recordset.Fields("Hyperlink").Value = $Hyperlink
	$Recordset.Update
	;? "Update share record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close share recordset " + @ERROR + ": " + @SERROR Get $x
Next
 
;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)
;? "Get simple adapter info from WMI " + @ERROR + ": " + @SERROR Get $x
For Each $Card In $NetCards
	$DeviceID = $Card.Index
	;? "Device ID is " + $DeviceID
	$MACAddress = $Card.MACAddress
	;? "MAC Address is " + $MACAddress
	$NetCard = $Card.Description
	;? "Net card description is " + $NetCard
	$IPAddress = $Card.IPAddress
	;? "IP Address is " + $IPAddress[0]
	$DHCP = $Card.DHCPEnabled
	If $DHCP
		$DHCP = "DHCP Enabled"
	Else
		$DHCP = "Static address"
	Endif
	;? "DHCP state is " + $DHCP
	$SQL_NETWORK = "SELECT * FROM TBL_NETWORKADAPTERS WHERE COMPID=" + $PCIDENTIFIER + " AND DEVICEID=" + $DeviceID + ";"
	$Command.CommandText = $SQL_NETWORK
	$Recordset.Open($Command)
	;? "Open Adapters recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
		;? "Add new adapters record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("DeviceID").Value = $DeviceID
	$Recordset.Fields("NetCard").Value = $NetCard
	$Recordset.Fields("IPAddress").Value = $IPAddress[0]
	$Recordset.Fields("MACAddress").Value = $MACAddress
	$Recordset.Fields("DHCP").Value = $DHCP
	$Recordset.Update
	;? "Update net card record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close net card recordset " + @ERROR + ": " + @SERROR Get $x
Next

;Collect Printer Connection info - should grab local as well
$WMI_PRINTERS = "SELECT * FROM WIN32_PRINTER"
$Printers = GetObject($WMI).ExecQuery($WMI_PRINTERS)
	;? "Get printers from WMI " + @ERROR + ": " + @SERROR Get $x
For Each $Printer In $Printers
	$PrinterID = $Printer.DeviceID
	$DriverName = $Printer.DriverName
	$PortName = $Printer.PortName
	$Description = $Printer.Description
	$SQL_PRINTERS = "SELECT * FROM TBL_PRINTERCONNECTIONS WHERE COMPID=" + $PCIdentifier + " AND PRINTERID='" + $PrinterID + "'"
	$Command.CommandText = $SQL_PRINTERS
	$Recordset.Open($Command)
	;? "Open printer recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
	;? "Add new printer record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("PrinterID").Value = $PrinterID
	$Recordset.Fields("DriverName").Value = $DriverName
	$Recordset.Fields("PortName").Value = $PortName
	$Recordset.Fields("Description").Value = $Description
	$Recordset.Update
	;? "Update new printer record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close printer recordset " + @ERROR + ": " + @SERROR Get $x
Next

;Collect info on user's network drives
$WMI_MAPPEDDRIVES = "SELECT * FROM WIN32_LOGICALDISK WHERE DRIVETYPE=4"
$MappedDrives = GetObject($WMI).ExecQuery($WMI_MAPPEDDRIVES)
For Each $Drive in $MappedDrives
	$Letter = $Drive.Name
	$Path = $Drive.ProviderName
	$SQL_MAPPEDDRIVES = "SELECT * FROM TBL_MAPPEDDRIVES WHERE USERID=" + $UserIdentifier + " AND LETTER='" + $Letter + "'"
	$Command.CommandText = $SQL_MAPPEDDRIVES
	$Recordset.Open($Command)
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
	Endif
	$Recordset.Fields("UserID").Value = $UserIdentifier
	$Recordset.Fields("Letter").Value = $Letter
	$Recordset.Fields("Path").Value = $Path
	$Recordset.Update
	$Recordset.Close()
Next

;Close connection to database
;$Connection.Close()
;? "Close database connection " + @ERROR + ": " + @SERROR Get $x

;Tidy up by releasing COM objects from memory
$Connection = 0
$Recordset = 0
$Command = 0  

;FUNCTION     WMIQuery
;
;ACTION        Queries WMI information from supported systems
;
;AUTHOR     Radimus
;
;CONTRIBUTORS    kdyer, Shawn, And Howard
;
;VERSION     2.4
;
;DATE CREATED     12/22/2001
;
;DATE MODIFIED    09/23/2003
;
;KIXTART     4.x
;
;SYNTAX     WMIQuery($what,$from,optional $computer,optional $where, optional $x)
;
;PARAMETERS     $what
;            
;
;         $from
;            Win32 Collection
;
;        optional $computer
;            defaults to local PC
;
;        optional $where
;            addl parameter for a 'WHERE' clause. Used with $x
;
;        optional $x
;            addl parameter for a 'WHERE' clause. Used with $Where
;
;
;RETURNS     Array 
;        @error 1 = Cannot create COM object on target PC
;
;REMARKS    This is chage alters the return from the function into an ARRAY, where the previous version
;        was a pipe '|' delimited string.  If you are updating to this version, check your code closely
;
;DEPENDENCIES     kix 4.x+, WMI
;
;EXAMPLE     $make  = WMIQuery("Manufacturer","Win32_ComputerSystem")[0]
;        $modem = WMIQuery("Description","Win32_POTSModem",$remotePC,"Status","OK")[0]
;        for each $stick in WMIQuery("Capacity","Win32_PhysicalMemory")
;            ? val($stick) / 1048576
;            next
;
;KIXTART BBS     http://www.kixtart.org/board/ultimatebb.php?ubb=get_topic;f=12;t=000117
;        http://download.microsoft.com/download/platformsdk/wmicore/1.5/W9XNT4/EN-US/wmicore.EXE 

;Many thanks to the authors of the WMIQuery UDF for this - two gents known as Radimus and Kent
FUNCTION WMIQuery($what,$where,)
	Dim $strQuery, $objEnumerator, $value	
	$strQuery = "Select $what From $where"	
	$SystemSet = GetObject($WMI)
	$objEnumerator = $SystemSet.ExecQuery($strQuery)	
	For Each $objInstance In $objEnumerator
		If @Error = 0 and $objInstance <> ""			
			$x=execute("$$value = $$objInstance.$what")			
			$WMIQuery="$value" + "|" + "$WMIQuery"			
		EndIf		
	Next	
	$WMIQuery=left($WMIQuery,len($WMIQuery)-1)	
	exit @error		
ENDFUNCTION

HTH,

Kent

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

Top

#109532 - 2003-12-04 06:40 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Well before I run this i need it to connet to a SQL database not access. Can I just change the dbconnect or will that mess up the rest of the script?
Top

#109533 - 2003-12-04 07:09 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	OK I modified to suit my SQL server environment. I run the script and it errors out on line 46 which is this. Code: If @ERROR <> 0 GoTo ADO_Error EndIf It says something about that not existing. Please help.
Top

#109534 - 2003-12-04 07:20 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Would this be correct format for the script? Code: $DSN="Driver={SQL Server};" $DSN=$DSN+"Server=Sussex-SQL;" $DSN=$DSN+"Database=Logins;" $DSN=$DSN+"Uid=logins;" $DSN=$DSN+"Pwd=logins;"
Top

#109535 - 2003-12-04 07:20 PM Re: Does Anyone Have A Good Working Asset Management S
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	DJ, Just change the connect string to fit your db.. For the: Code: If @ERROR <> 0 GoTo ADO_Error EndIf Just delete the lines or comment (;) them out.. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#109536 - 2003-12-04 07:20 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	It also appears that there is no tables created so where does it update the data to in the database? I am really new at this.
Top

#109537 - 2003-12-04 07:22 PM Re: Does Anyone Have A Good Working Asset Management S
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	What happens when you try it? Does it connect? You may notice there are a lot of lines with - ;? "Create Connection object " + @ERROR + ": " + @SERROR Get $x For example.. Uncomment them and re-run the script.. You should be able to debug it.. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#109538 - 2003-12-04 07:26 PM Re: Does Anyone Have A Good Working Asset Management S
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	You have to have the tables and the database to do this.. No tables, improper data types, will cause this to fail. You should be able to roll back your sleeves, take your code and integrate what has been given to you. You are going to have to do some of this yourself. In other words, what I am trying to say is that my code would not work with your current database structure. I only posted it (and probably too much) to show the db connection. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#109539 - 2003-12-04 07:27 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	OK I didn't have to comment out the ADO error line. Now when I execute the script it comes up with the command prompt screen with no writing and then exits. Nothing in the database either though. I created the database and setup a user to be able to edit it. But like I said there doesn't seem to be any tables in the database that the data flows into from the kixscript. What am I missing?
Top

#109540 - 2003-12-04 07:32 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	OK I have uncommented to the point where it fails and it doesn't have the tables. So I will work with it and see what I can come up with. I'll keep you posted. I didn't realize I would have to create the tables manually. The previous script did it for me. But that is ok.
Top

#109541 - 2003-12-04 07:46 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Alright i have the tbl_users and tbl_computer tables created and the database gets passed these. Some data is filtering into the fields however I can't figure out why so many of them are showing NULL. I know the columns are allowing NULL by default but is your script grabbing the information in the right places on the PC's?
Top

#109542 - 2003-12-04 08:13 PM Re: Does Anyone Have A Good Working Asset Management S
Jose Jose Seasoned Scripter Registered: 2001-04-04 Posts: 693 Loc: Buenos Aires - Argentina	You could replace all the variables with text so as to see weather your problem comes from empty values from the script or from sql. You can comment the actual ones and try something like this instead. $Recordset.Fields("compID").Value = "Test" _________________________ Life is fine.
Top

#109543 - 2003-12-04 08:15 PM Re: Does Anyone Have A Good Working Asset Management S
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	I got it, it was SQL problem. Datatypes were wrong. Everything is fine now. My final problem is a dreamweaver issue, putting all of this in cold fusion format for viewing. And adding a search utility to find a user with computer name.
Top

Page 1 of 2

Previous Topic View All Topics

Index Next Topic

Moderator: Arend_, Allen, Jochen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org