PC Data to SQL Database - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Advanced Scripting » PC Data to SQL Database

Page 1 of 1

Topic Options

#137950 - 2005-04-14 07:06 AM PC Data to SQL Database
ClientMaster ClientMaster Fresh Scripter Registered: 2005-01-19 Posts: 46 Loc: Tokyo, Japan	I am still having problems... This is my code... can anyone help with this? CLS AT (1,1) "Obtaining Computer Information..." SLEEP 2 CLS ;BREAK ON CLS ;** DECLARE VARIABLES * DIM $CNstring, $CMDtxt, $cn, $cmd, $rs, $ModifyDateTime, $username, $osname, $PCModel, $Comment, $Language, $ServicePack, $SysManufacturer, $SysSerial, $IPaddress, $macaddr ;* CONFIGURE DATABASE CONNECTION STRING * $cnstring = "DRIVER={SQL Server};SERVER=ServerNAME;UID=UID;PWD=PW;DATABASE=DBNAME" $CMDtxt = "select * from COMPUTERS where pcname = '@WKSTA'" $cn=CreateObject("ADODB.Connection") $cmd=CreateObject("ADODB.Command") $rs=CreateObject("ADODB.RecordSet") $cn.connectionstring = $CNstring $cn.Open $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) ;* COLLECT WORKSTAION ASSET INFORMATION $ModifyDateTime = @DATE + " " + @TIME $pcname = @WKSTA $username = @userid $Comment = @Comment $IPaddress = @IPADDRESS0 $osname = WMIQuery("Caption","Win32_OperatingSystem") $Language = @syslang $ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem") $SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem") $PCModel = WMIQuery("Model","Win32_ComputerSystem") $SysSerial = WMIQuery("SerialNumber","Win32_BIOS") ; *** MAC ADDRESS INFO *** ; Issue 'IPCONFIG' to get the local IP address, also record the mac address IF (@DOS < "5.0") GOTO #ADU5 ENDIF if exist("c:\ip.txt") SHELL "%COMSPEC% /c del c:\ip.txt" ENDIF shell "cmd.exe /c %WINDIR%\SYSTEM32\ipconfig.exe /all > c:\ip.txt" IF EXIST("c:\ip.txt") else goto #ADU4A ENDIF $macaddr="00-00-00-00-00-00" $ipdone=0 IF OPEN(4,"c:\ip.txt") = 0 WHILE (@ERROR=0) and ($ipdone=0) $ipline=Readline(4) ; processing for 'Physical Address. . . ' lines ; comes before 'ip address' line per adapter If INSTR($ipline,"Physical Address. .") $l=LEN($ipline) $m=instr($ipline,":") IF ($m=0) or ($m=$l) ELSE $macaddr=substr($ipline,$m+1,$l-$m) $macaddr=Trim("$macaddr") ENDIF EndIf LOOP $cl=CLOSE(4) ENDIF :#ADU4A ; compatibility with AD migration checking if $lanaddr="0.0.0.0" $lanaddr="unknown" $macaddr="unknown" Endif :#ADU5 :#ADEND FUNCTION WMIQuery($what,$where,) dim $strQuery, $objEnumerator, $value $strQuery = "Select $what From $where" $SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//"+@WKSTA) $objEnumerator = $SystemSet.ExecQuery($strQuery) For Each $objInstance in $objEnumerator If @Error = 0 and $objInstance <> "" $=execute("$$value = $$objInstance.$what") $WMIQuery="$value"+"\|"+"$WMIQuery" EndIf Next $WMIQuery=left($WMIQuery,len($WMIQuery)-1) exit @error ENDFUNCTION ;* ADD RECORDS TO THE DATABASE *** If $rs.eof = -1 ; addnew is only needed if a record for this workstation was not found. $rs.addnew EndIf $rs.fields.item("pcname").value = $pcname $rs.fields.item("username").value = $username $rs.fields.item("Comment").value = $Comment $rs.fields.item("lastlogin").value = $ModifyDateTime $rs.fields.item("osname").value = $osname $rs.fields.item("LANGUAGE").value = $Language $rs.fields.item("SERVICEPACK").value = $ServicePack $rs.fields.item("PCMANUFACTURER").value = $SysManufacturer $rs.fields.item("PCModel").value = $PCModel $rs.fields.item("SYSSERIAL").value = $SysSerial $rs.fields.item("IP").value = $IPaddress $rs.fields.item("MACADDRESS").value = $macaddr $rs.update $rs.Close
Top

#137951 - 2005-04-14 07:22 PM

Re: PC Data to SQL Database

Kdyer

KiX Supporter

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

You can clean up your code significantly with Win32_NetworkAdapter..

Code:


 CLS
 AT (1,1) "Obtaining Computer Information..."
 SLEEP 2
 CLS

 ;BREAK ON CLS

 ;**** DECLARE VARIABLES ***

 DIM $cnstring, $cmdtxt, $cn, $cmd, $rs, $modifydatetime, $username, $osname, $pcmodel, $comment, $language,
 $servicepack, $sysmanufacturer, $sysserial, $ipaddress, $macaddr

 ;*** CONFIGURE DATABASE CONNECTION STRING ***
 $cnstring = "DRIVER={SQL Server};SERVER=ServerNAME;UID=UID;PWD=PW;DATABASE=DBNAME"

 $cmdtxt = "select * from COMPUTERS where pcname = '@WKSTA'"
 $cn=CreateObject("ADODB.Connection")
 $cmd=CreateObject("ADODB.Command")
 $rs=CreateObject("ADODB.RecordSet")

 $cn.connectionstring = $cnstring
 $cn.open
 $cmd.activeconnection = $cn
 $rs.cursortype = 3
 $rs.locktype = 3
 $rs.activecommand = $cmd

 $cmd.commandtext = $cmdtxt $rs.open ($cmd)

 ;*** COLLECT WORKSTAION ASSET INFORMATION

 $modifydatetime = @date + " " + @time
 $pcname = @wksta
 $username = @userid
 $comment = @comment
 $ipaddress = @ipaddress0
 $osname = WMIQuery("Caption","Win32_OperatingSystem")
 $language = @syslang
 $macaddr = WMIQuery("MACAddress","Win32_NetworkAdapter")
 $servicepack = WMIQuery("CSDVersion","Win32_OperatingSystem")
 $sysmanufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
 $pcmodel = WMIQuery("Model","Win32_ComputerSystem")
 $sysserial = WMIQuery("SerialNumber","Win32_BIOS")

 ; compatibility with AD migration checking
 IF $lanaddr="0.0.0.0"
    $lanaddr="unknown"
    $macaddr="unknown"
 ENDIF

 FUNCTION WMIQuery($what,$where,)
    DIM $strquery, $objenumerator, $value
    $strquery = "Select $what From $where"
    $systemset = GetObject("winmgmts:{impersonationLevel=impersonate}!//"+@wksta)
    $objenumerator = $systemset.execquery($strquery)
    FOR EACH $objinstance IN $objenumerator
       IF @error = 0 AND $objinstance <> ""
          $=execute("$$value = $$objInstance.$what")
          $wmiquery="$value"+"|"+"$WMIQuery"
       ENDIF
    NEXT
    $wmiquery=left($wmiquery,len($wmiquery)-1)
    EXIT @error
 ENDFUNCTION

 ;*** ADD RECORDS TO THE DATABASE ***

 IF $rs.eof = -1 ; addnew is only needed if a record for this workstation was not found.
    $rs.addnew
 ENDIF

 $rs.fields.item("pcname").value = $pcname
 $rs.fields.item("username").value = $username
 $rs.fields.item("Comment").value = $comment
 $rs.fields.item("lastlogin").value = $modifydatetime
 $rs.fields.item("osname").value = $osname
 $rs.fields.item("LANGUAGE").value = $language
 $rs.fields.item("SERVICEPACK").value = $servicepack
 $rs.fields.item("PCMANUFACTURER").value = $sysmanufacturer
 $rs.fields.item("PCModel").value = $pcmodel
 $rs.fields.item("SYSSERIAL").value = $sysserial
 $rs.fields.item("IP").value = $ipaddress
 $rs.fields.item("MACADDRESS").value = $macaddr

 $rs.update

 $rs.close

Also, removed the GOTOs..

Of course, this is assuming that you have the SQL Tables and Identity Seed(s) setup correctly.

Thanks,

Kent

Edited by kdyer (2005-04-14 07:25 PM)

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

Top

#137952 - 2005-04-15 06:58 PM Re: PC Data to SQL Database
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	Also, you can simplify this even further.. $macaddr = WMIQuery("MACAddress","Win32_NetworkAdapter") if you want to have this run for a local or remote machine. But, with the Code you have, you can replace it to be simply: $macaddr=@ADDRESS Thanks, Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#137953 - 2005-04-16 02:23 AM Re: PC Data to SQL Database
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	You might also want to consider to switching to a purely SQL-based interaction with the database, e.g. via DBCommand(). _________________________ There are two types of vessels, submarines and targets.
Top

#137954 - 2005-04-16 03:35 AM Re: PC Data to SQL Database
ClientMaster ClientMaster Fresh Scripter Registered: 2005-01-19 Posts: 46 Loc: Tokyo, Japan	Thank you all very much. First, the Win32_NetworkAdapter idea is great. Will that show all network adapters? I tried running it once before and it gave me several 00-00-00 type mac addresses and mine... Secondly, if I could do it totally SQL, I would, but I have no idea about SQL... The problem that I really want to work out first is how to use this code and connect to SQL DB. Maybe it needs to be pure SQL?
Top

#137955 - 2005-04-16 04:14 AM Re: PC Data to SQL Database
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	If you look at the example in the updated UDF for WMIQUERY() in the UDF section, Rad provides a good example of trying to find all of the memory sticks. Just use the same methodology for MacAddress. I think NTDoc addresses multiple Nic/Adapters in his Helpdesk scripts as well. Pure SQL, just keep whittling away on it and we'll get you there. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#137956 - 2005-04-17 12:05 AM Re: PC Data to SQL Database
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Pure SQL: Code: INSERT INTO TABLE1 (column_1,column_2) VALUES ('Value1','Value2') UPDATE authors SET authors.au_fname = 'Annie' WHERE au_fname = 'Anne' _________________________ There are two types of vessels, submarines and targets.
Top

#137957 - 2005-04-17 02:26 AM

Re: PC Data to SQL Database

Kdyer

KiX Supporter

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

Here are some more variations on this..

For the Update, you could use:
Code:


UPDATE a 
SET a.storage_co = c.storage_co
FROM dbo.shp a 
	INNER JOIN dbo.gen b 
		ON a.[file_id] = b.[file_id]
	INNER JOIN KDyer.hold_storage c
		ON c.loan_num = b.loan_num

or:
Code:


UPDATE 
    SHP 
    SET shp.storage_co=
(SELECT     KDyer.hold_storage.storage_co
FROM         KDyer.hold_storage INNER JOIN
                      dbo.gen ON KDyer.hold_storage.loan_num = dbo.gen.loan_num)
FROM         dbo.shp INNER JOIN
                      dbo.gen ON dbo.shp.file_id = dbo.gen.file_id

One variation for Inserts
Code:


INSERT translog 
    (
        file_id,
        employ_id,
        field,
        oldval,
        newval, 
        changdate
    ) 
SELECT     a.file_id,'ADP','Storage Company', a.storage_co, c.storage_co,getdate()
FROM         dbo.shp a INNER JOIN
                      dbo.gen b ON a.file_id = b.file_id INNER JOIN
                      hold_storage c ON c.loan_num = b.loan_num

Doing three inserts at once:
Code:


INSERT translog 
    (
        file_id,
        employ_id,
        field,
        oldval,
        newval, 
        changdate
    ) 
SELECT     a.file_id,'ADP','Storage Company', a.storage_co, c.storage_co,getdate()
FROM         dbo.shp a INNER JOIN
                      dbo.gen b ON a.file_id = b.file_id INNER JOIN
                      hold_storage c ON c.loan_num = b.loan_num
UNION SELECT     a.file_id,'ADP','File Location', a.file_location, c.file_location,getdate()
FROM         dbo.shp a INNER JOIN
                      dbo.gen b ON a.file_id = b.file_id INNER JOIN
                      hold_storage c ON c.loan_num = b.loan_num
UNION SELECT     a.file_id,'ADP','Package Track No', a.pkg_trac, c.pkg_trac,getdate()
FROM         dbo.shp a INNER JOIN
                      dbo.gen b ON a.file_id = b.file_id INNER JOIN
                      hold_storage c ON c.loan_num = b.loan_num

HTH,

Kent

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

Top

#137958 - 2005-06-14 08:13 PM

Re: PC Data to SQL Database

DJ Ballistic Offline

Starting to like KiXtart

Registered: 2003-02-21
Posts: 185

I am trying to use this script above to capture data and dump it to an SQL database. I have the SQL database created with the necessary table and fields.
I will post my code below but I wanted to inform that the only data that is being displayed or captured is this:
FullName, PCName, UserName, Comment, LastLogin, Language (looks funky though), IP, & MACAddress. It misses the rest. Can anyone help? I have a couple of things commented out because I am no longer using them. However the Dell Service Tag code if i use that then i can capture that SysSerial info.

Code:

 

;Call @LSERVER+ "\NETLOGON\GetUserOU.udf"

CLS
At (1,1) "Obtaining Computer Information..."
Sleep 2
CLS
Break On CLS

;**** Declare Variables ****
Dim $cnstring, $cmdtxt, $cn, $cmd, $rs, $modifydatetime, $username, $osname, $pcmodel, $comment, $language,
$servicepack, $sysmanufacturer, $sysserial, $ipaddress, $maccaddr

;**** Configure Database Connection String ****
$CNstring = "DRIVER={SQL Server};SERVER=Sussex-SQL;UID=warranty;PWD=warranty;DATABASE=Warranty"
$CMDtxt = "select * from COMPUTERS where PCName = '@WKSTA'"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring 
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3 
$rs.locktype = 3 
$rs.activecommand = $cmd

$cmd.commandtext = $CMDtxt $rs.Open ($cmd)

;**** Collect Workswtation Asset Information ****
$ModifyDateTime = @DATE + " " + @TIME
$pcname = @WKSTA
$username = @USERID
$comment = @COMMENT
$ipaddress = @IPADDRESS0
$osname = WMIQuery("Caption", "Win32_OperatingSystem")
$language = @SYSLANG
$macaddr = @ADDRESS
$servicepack = WMIQuery("CSDVersion", "Win32_OperatingSystem")
$sysmanufacturer = WMIQuery("Manufacturer", "Win32_ComputerSystem")
$pcmodel = WMIQuery("Model", "Win32_ComputerSystem")
$sysserial = WMIQuery("SerialNumber", "Win32_BIOS")

; **** Compatibility with AD migration checking ****
If 	$lanaddr="0.0.0.0"
	$lanaddr="unknown"
	$macaddr="unknown"
EndIf

Function WMIQuery($what,$where,)
	Dim $strquery, $objenumerator, $value
	$strquery = "Select $what From $where"
	$systemset = GetObject("winmgmts:{impersonationLevel:impersonate}!//"+@WKSTA)
	$objenumerator = $systemset.execquery($strquery)
	For Each $objinstance IN $objenumerator
		IF @error = 0 AND $objinstance <> ""
			$=Execute("$$value = $$objinstance.$what")
			$wmiquery="$value"+"|"+"$WMIQuery"
		EndIF
	Next
	$wmiquery=Left($wmiquery,Len($wmiquery)-1)
	Exit @ERROR
EndFunction

;**** Add Records to the database ****

If $rs.eof = -1 ; Addnew is only needed if a record for this workstation was not found.
   $rs.addnew
EndIf	

$rs.fields.item("PCName").value = $pcname 
$rs.fields.item("UserName").value = $username 
$rs.fields.item("Comment").value = $comment 
$rs.fields.item("LastLogin").value = $modifydatetime 
$rs.fields.item("OSName").value = $osname 
$rs.fields.item("Language").value = $language 
$rs.fields.item("ServicePack").value = $servicepack 
$rs.fields.item("PCManufacturer").value = $sysmanufacturer 
$rs.fields.item("PCModel").value = $pcmodel 
$rs.fields.item("SysSerial").value = $sysserial 
$rs.fields.item("IP").value = $ipaddress 
$rs.fields.item("MACAddress").value = $macaddr 
$rs.update 
$rs.Close

;**********************
; Dell Service Tag Code
;**********************
;$objs = GetObject("WinMgmts:").InstancesOf("Win32_SystemEnclosure") 
;For Each $obj In $objs 
; 	$ServiceTag = $obj.SerialNumber 
;Next
;$SMS_ID=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Client\configuration\Client Properties\","SMS Unique Identifier")

Sleep 5

Top

#137959 - 2005-06-14 08:21 PM Re: PC Data to SQL Database
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Sorry forgot to save the script before uploading it - I am capturing FullName with this defined above: $fullname = @FULLNAME and with this below: $rs.fields.item("FullName").value = $fullname
Top

#137960 - 2005-06-14 08:28 PM Re: PC Data to SQL Database
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	OK I performed some error checking and this is what is happening when it is attempting to add the records into the database. 52567/80020009]llection corresponding to the requested name or ordinal.) [-21473
Top

#137961 - 2005-06-14 09:40 PM Re: PC Data to SQL Database
maciep maciep Korg Regular Registered: 2002-06-14 Posts: 947 Loc: Pittsburgh	think you might want to add $ = setoption('wrapateol','on') to the top of your script - output might be readable then...
Top

#137962 - 2005-06-14 09:55 PM Re: PC Data to SQL Database
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	think you might want to start your own thread and not hijaak this one. _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#137963 - 2005-06-14 10:02 PM Re: PC Data to SQL Database
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	I am continuing off of this one because it is using the code used in the beginning on this thread. And the error now with that wrap code entered is this: Error = -2147352567 - COM exception error "value" (Microsoft OLE DB Provider for ODBC Drivers - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.) [-2147352567/80020009] I have no idea what this means.
Top

#137964 - 2005-06-14 10:10 PM Re: PC Data to SQL Database
DJ Ballistic DJ Ballistic Starting to like KiXtart Registered: 2003-02-21 Posts: 185	Please disregard - I have corrected the problem.
Top

#176837 - 2007-06-08 04:40 PM

Re: PC Data to SQL Database [Re: DJ Ballistic]

Radimus Offline

Moderator

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL

ressurecting the dead...

I'm working on the same sort of thing, but I'm having issues $\:\)$

Code:

break on
$ = setoption('wrapateol','on')

$cnstring = "DRIVER={SQL Server};SERVER=wheeler-sqlsrv;UID=Inventory;PWD=kixscript;DATABASE=inventory"

$cmdtxt = "select * from _tbl_Main where Wksta = '@WKSTA'"
$cn=CreateObject("ADODB.Connection")
$cmd=CreateObject("ADODB.Command")
$rs=CreateObject("ADODB.RecordSet")
? 'Error = '+@ERROR+' - '+@SERROR

$cn.connectionstring = $cnstring
$cn.open
? 'Error = '+@ERROR+' - '+@SERROR

$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
? 'Error = '+@ERROR+' - '+@SERROR

$cmd.commandtext = $cmdtxt $rs.open ($cmd)
? 'Error = '+@ERROR+' - '+@SERROR


;*** ADD RECORDS TO THE DATABASE ***

IF $rs.eof = -1 ; addnew is only needed if a record for this workstation was not found.
	$rs.addnew
ENDIF

$rs.fields.item("wksta").value = '"'+@wksta+'"'
$rs.fields.item("Model").value = "PC Model"
$rs.fields.item("IPAddr").value = '"'+@ipaddress0+'"'
$rs.fields.item("UserID").value = '"'+@userid+'"'

$rs.update
? 'Error = '+@ERROR+' - '+@SERROR

$rs.close

returns

Code:

Error = 0 - The operation completed successfully.
Error = 0 - The operation completed successfully.
Error = -2147352562 - Invalid number of parameters.
Error = 0 - The operation completed successfully.
Error = 0 - The operation completed successfully.

and it always writes new records to the DB, no updates

Suggestions?

_________________________
How to ask questions the smart way <-----------> Before you ask

Top

#176838 - 2007-06-08 04:53 PM Re: PC Data to SQL Database [Re: Radimus]
Richard H. Richard H. Administrator Registered: 2000-01-24 Posts: 4946 Loc: Leatherhead, Surrey, UK	ActiveCommand is a read-only property according to the following page: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdproactivecommand.asp
Top

#176840 - 2007-06-08 06:20 PM Re: PC Data to SQL Database [Re: Richard H.]
Radimus Radimus Moderator Registered: 2000-01-06 Posts: 5187 Loc: Tampa, FL	ok, well my problem now is that it doesn't update existing records, it creates new duplicates _________________________ How to ask questions the smart way <-----------> Before you ask
Top

#176842 - 2007-06-08 06:45 PM Re: PC Data to SQL Database [Re: Radimus]
Radimus Radimus Moderator Registered: 2000-01-06 Posts: 5187 Loc: Tampa, FL	ok... got it... It was inserting the quotes into the values I was updating, and the select wasn't looking for the inserted quotes Code: $rs.fields.item("wksta").value = @wksta $rs.fields.item("Model").value = "PC Model" $rs.fields.item("IPAddr").value = @ipaddress0 $rs.fields.item("UserID").value = @userid _________________________ How to ask questions the smart way <-----------> Before you ask
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

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

Hop to:

Shout Box

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

	Web kixtart.org