Page 4 of 5 <12345>
Topic Options
#208125 - 2013-12-04 11:55 AM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
The code is enumerating an array. You replaced the line that displays the result with one that stores the result in a variable:
 Code:
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $CDKey = $Product + ': ' + $Key
;            ? $Product + ': ' + $Key
thus, at the end of the For/Next loop, the $CDKey will contain only the last product/key value pair. You either need to use the array or replace the assignmentn with a database write.

Reading back to the beginning, you started with something that was not optimally structured, and seem to have continued on that path with your changes - not good... I'll be back later with some suggestions - I'll need some time to digest this.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208126 - 2013-12-04 01:45 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
OK - having had a few minutes to look at this, I'd start with pencil & paper and sketch out the flow of the key tasks.. (or - Notepad and keyboard). Something like:
 Code:
Declare vars

Define initial values

Describe the key tasks and identify UDFs to accomplish them

Identify Computer (@WKSTA)

Query database
  is this computer listed?
    If so, was the last update more than 6 months ago?
      If NOT, exit - nothing to do

Need to perform an inventory

Clear/Init the NEWDATA array

Query for the following items and load the NEWDATA array with the results. 
 O/S Version
 Hardware Platform
 Installed Software
Use a UDF (such as WMYSysInfo) to query for this info. Extend the function to obtain other required parameters, such as....

For debugging, write the data to a .CSV file for viewing in Excel (CSV.kxf to convert between Array and CSV string)

Use DBLib.udf to write/update the database with the data in the NEWDATA Array

Log Errors/Results to C:\TEMP\INVENT.LOG (fMSG.kxf for timestamped logging)
Now that you have an outline of what you want to do, you can start writing code! Working without such a plan will not lead to success. I start every project with such a document. I always start with a file called BASE.TXT, which has some key info/commands that I put in every script. I copy it to whatever scriptname I'm planning to use, and then add the information above as widely spaced comments. This keeps me focused on a single goal for each section of code.

I also use a Kix development application to create almost every Kix script. You can download the Kix Development Suite from our website and use the KGEN.BAT file to help generate and validate your code. I won't explain further as it's fully documented in the KGen User Guide and installation README file. It's free to download and use and includes our entire Kixtart function library.

KGen does make the following best practices easier to use -

Create a header file (scriptname.TXT) to identify the script, author, & version info. MAINTAIN THE VERSION INFO!! Use this file to set program options (SetOption() calls), declare any global vars, and any vars used by the main program.

Create one or more script files (##_scriptname_purpose.KXF) to define specific blocks of code. For example, I have a GUI script with two tab pages. I have the following files defined:
- scriptname.txt Initialization
- 01_FormMain.kxf Primary form definitions
- 02_FormTab1.kxf First tab form definitions
- 02_FormTab2.kxf Second tab form definitions
- 05_ProgMain.kxf The code that ties the app together
- 06_ProgTab1.kxf First tab function definitions
- 06_ProgTab2.kxf Second tab function definitions
- 10_ProgFunc.kxf app-specific functions common to the entire app

So - I have 8 files in my application development folder. This keeps all of the related code together. KGen will assemble these into a complete script, including any of the external UDFs they might reference. The completed script is several thousand lines of code, but none of the files I work on are more than a few hundred. Much easier to work with, and prevents all kinds of mistakes. If you don't use KGen, write a simple script to locate the .TXT and all .KXF files in your dev folder and combine them into a single .KIX script file. You'll need to deal with the external functions by placing them into your dev folder, but this gets messy as you use them in more and more projects.

Put the functions you download into a KixtartUDFs folder.
- NEVER modify these UDFs - treat them as if they were part of Kix!
- NEVER remove the UDF headers from your scripts - they provide important information when debugging!
- If a public function give you 80-90% of what you need, DO NOT EDIT THE FUNCTION! Create a copy of the file, give it a new file and function name, add the features you need and update the header to reflect the changes you made. Use the modified UDF in your code.

Use single quotes for Kix. Kix will understand both single and double quotes.. Other languages you might call, such as a Shell to the command processor, require double quotes. Using single quotes in Kix allows you to embed the double-quotes easily into the strings used to call Shell commands, SQL statements, etc.

Be consistent! If you look at any of my code, you can find similarities between each of the files. My preferences are:
- ALL CAPS for Macros and Global vars.
- MixedCase for local vars
- Form objects always start with a lower-case 3 character class identifier; and include some type of text to identify which form page/module it is associated with. This helps clarify the CLEAR button on the menu from the buttons on tabs 1 and 2, for example.
- Indent each loop 2 spaces (enough to be visible, not so much that you need a 70" monitor to see the entire line!) ;\)

Use comments - explain what you did and why. This helps not only when others review your code, but when you have to come back in 6 months to implement a requested enhancement. My comment to code ratio in larger projects is typically 1.3:1 - 1.3 lines of comment for every line of code!

Use reasonable spacing and extra line breaks between logical blocks.

In summary, this won't directly fix the script you're using. You're starting with something that's 9+ years old, was not written following these best practices, but yet does what it was designed to do. If you want to use this in production, extract the best pieces of the original code and create a new, production-worthy app from scratch - something that will be reliable, self documented, and easy to maintain/troubleshoot/update.

Glenn

PS - here's my BASE.TXT file:
 Code:
; PROGRAM_NAME - DESCRIPTION
; Version #.# - AUTHOR - DATE
; 

Break On


; Declare variables
; ======================================================================

Dim $Rc						; Return code catcher
Dim $						; temp var

Global $DEBUG					; Debug flag
Global $VERSION					; version string
Global $MSG_LOG_, $ERR_LOG_			; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('WrapAtEOL', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')




; Define values
; ======================================================================
$DEBUG = 0
$VERSION = '0.0'



; MAIN Code
; ======================================================================
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208127 - 2013-12-04 02:44 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi Glenn,

thanks for this nice Howto. Ill try my best to get the best practice way and get a new clean script.

Will be back with my new code, soon

Thanks
Backfight

Top
#208128 - 2013-12-04 03:03 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Cool - the original code is minimally commented and hard to debug.. starting fresh will let us better help you.
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208140 - 2013-12-09 11:24 AM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi Glenn,

im so sorry but i dont get your kix dev running. I read the manual pdf but i dont get it.

So i started to write a "new" structured script with parts of the old code and get to my limit of knowledge in kix-scripting. i had fixed problem by problem, but at least i get an "Fatal exception occurred.". I wanna use your Memory UDF but this UDF runs into errors.
I wanna use the DBLib.udf but dont understand witch parts i need and what i have to modify.
I hope the new sturctured script helps you to help me debugging. There is many code out commented, for troubleshooting.

 Code:
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;

 
DIM $DBpath

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = "\\server\...\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

BREAK ON CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================

$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@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)

Break On

; Declare variables
; ======================================================================

DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj


Global $DEBUG				; Debug flag
Global $VERSION				; version string
Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('WrapAtEOL', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 0
$VERSION = '0.0'

; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


; MAIN Code
; ======================================================================


; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance, $objLocator 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $ = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================
 
 Dim $RegArray, $RegView, $Value,$Product,$Key, $rc, $Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $CDKey = $Product + ': ' + $Key
;            ? $Product + ': ' + $Key				; Wanna get this results in diferent Rows in the MDB Database
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;;FUNCTION		Memory() 
;; 
;;AUTHOR		Glenn Barnas
;; 
;;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
;; 
;;SYNTAX		Memory([system]) 
;; 
;;VERSION		4.0 
;; 
;;DATE			v1.0 - 2004/02/04
;; 
;;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
;; 
;;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;;
;;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;;			or video adapters and this memory is not reported. This can be adjusted for using
;;			the second example below.
;; 
;;RETURNS		Integer - Available Physical RAM (in Megabytes) 
;; 
;;DEPENDENCIES		None 
;; 
;;TESTED WITH		WinXP, Vista, Win7, Win8
;;			Windows Server 2000, 2003, 2008, 2012
;;			Tested with up to 16G of RAM 
;; 
;;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;;			
;;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;;			; (e.g. 0.5 instead of 512). 
;;			$MSize = 512			; Size of smallest RAM module (DIMM)
;;			
;;			$Mem = Memory()			; get reported memory
;;			$Ma  = $Mem / $MSize		; determine number of modules installed
;;			
;;			; If a fractional module is detected, add another full module
;;			; this accounts for a fractional module used for BIOS cache or video RAM
;;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;;			
;;			; $Mem now holds a value based more closely on installer rather than available RAM
;;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;;			
;; 
;
;Function Memory(Optional $_System, Optional $_fGB)
;
;  Dim $_MemMap				; Physical Memory Map
;  Dim $_Start, $_End, $_Step		; for/next start & step increment
;  Dim $_Sum				; running sum of memory from map
;  Dim $_Idx				; temporary index var
;  Dim $_iMem				; Memory region size index
;  Dim $_Error				; Error placeholder
;
;  ; Insure $_System has "\\System\" format if it is specified 
;  If $_System <> ''
;    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
;  EndIf

;  ; Get the memory value from the registry 
;  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
;  $_MemMap = ReadValue($_Idx, '.Translated')

;  ; Check for invalid read and Return 
;  If Len($_MemMap) = 0 Or @ERROR
;    $Memory = 0				; return 0 Mbytes 
;    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
;    Exit $_Error
;  EndIf

;  ; determine system's O/S type based on architecture
;  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
;  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
;    $_Step  = 40	; Define the step size based on the O/S Architecture
;    $_Start = 33	; Offset where memory descriptor blocks start
;    $_Sum   = 0.0	; no unreported base memory to account for
;    $_iMem  = 10	; Memory size index
;  Else
;    $_Step  = 32	; Define the step size based on the O/S Architecture
;    $_Start = 41	; Offset where memory descriptor blocks start
;    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
;    $_iMem  = 2		; Memory size index
;  EndIf
;  $_End = Len($_MemMap) - 8

;  For $_Idx = $_Start to $_End Step $_Step
;    $_Block = SubStr($_MemMap, $_Idx, $_Step)

;    If SubStr($_Block, $_iMem, 1) > 3
;      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
;    Else
;      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
;    EndIf
;  Next

;  ; Sum is in Bytes - return the total as megabytes
;  $Memory = CInt($_Sum / 1048576)
;  Exit 0

;EndFunction


; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SysModel = WMIQuery("Model","Win32_ComputerSystem")
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
$CPUDescription = $wmiObj.Name	
Next
Sleep 5
$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
;If $SysMemory = 0
;  $SysMemory = Memory()
;EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")
For Each $wmiObj in $wmiColl
$MonitorDescription = $wmiObj.Name
$MonHersteller = $wmiObj.MonitorManufacturer
next

; 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("MonHersteller").value = $MonHersteller
$rs.fields.item("MonitorDescription").value = $MonitorDescription
$rs.fields.item("IP").value = $IP
$rs.fields.item("Uhrzeit").value = $Uhrzeit
$rs.fields.item("MAC").value = $MAC
$rs.fields.item("PRIVILEGIEN").value = $Privilegien
$rs.fields.item("COMPUTERNAME").value = $computername
$rs.fields.item("OSNAME").value = $osname
$rs.fields.item("OSTYPE").value = $OSType
$rs.fields.item("SERVICEPACK").value = $ServicePack
$rs.fields.item("INSTALLDATE").value = $InstallDate
$rs.fields.item("LASTBOOTTIME").value = $LastBoottime
$rs.fields.item("OSSERIAL").value = $OSSerial
$rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
$rs.fields.item("SYSMODEL").value = $SysModel
$rs.fields.item("SYSSERIAL").value = $SysSerial
$rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
$rs.fields.item("CPUSPEED").value = $CPUSpeed
;$rs.fields.item("SYSMEMORY").value = $SysMemory
$rs.fields.item("VIDEOCARD").value = $VideoCard
$rs.fields.item("VIDEORES").value = $VideoRes
;$rs.fields.item("NICCARD").value = $NicCard
$rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
$rs.fields.item("USERNAME").value = $Username
$rs.fields.item("CDKey").value = $CDKey
$rs.update

$rs.Close


I hope i get near to the best practice with my code. if i dont, please correct me.

Thanks a lot

Backfight


Edited by backfight (2013-12-09 11:41 AM)

Top
#208141 - 2013-12-09 01:34 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
I changed the order of some of the code to follow the Description, Variable Declaration, Variable Definition, Main Code, Function Definition sequence. You still had some code that is intermingled with the UDFs... the code related to determining the CD Key contains Allen's example code and his UDFs. You need to review how the UDF and his example code works as right now, I think you will only get the last CD Key found added to the database.

The WMIQuery UDF is great when you need to get one or two WMI values, but is very inefficient when you need to gather a dozen or more. The UDF instantiates a new WMI connection with each call, which can take a second or more per call. Take a look at WMISysQuery(), which makes a single WMI instantiation and then fills an array with 40 of the most commonly needed system inventory values. Use that model to create a custom function if needed.

There's no need to comment-out a function (Memory). You can either simply delete it or ignore it - if you don't call it, it doesn't do anything.

I got rid of several duplicate references including "BREAK ON CLS", put the Break On at the top of the script, moved the comment describing the script into the header section.

To work with KGen, I'd suggest you install it on your workstation until you understand how it works. Create C:\DEV & C:\DEV\KixLib. Run the setup and specify C:\DEV\KixLib for the kixtart library path. You MUST run the install from a command prompt, and MUST close and re-open the command prompt for the utility to work as it changes the environment settings. These will only take effect when a new command window is opened.

Under D:\DEV, create a folder for your project (INVENTORY). Create Inventory.txt in that folder and copy the contents of the script below, from the beginning to the empty line after "Functions Follow" into it. Make sure you put any additional functions you downloaded from KORG (such as the CD Key UDF) into the KixLib folder. Then, simply run "KGEN INVENTORY" - it should generate the INVENTORY.KIX script with all needed UDFs, ready to run. Pay attention to the warnings it generates as it will identify undeclared variables, mismatched quotes and other paired objects, and similar code issues.

We offer free support for installation issues of KixDev and free and paid support for all our products through our web site. Feel free to contact us to work out your install/setup issues.
 Code:
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('WrapAtEOL', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 0
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = "\\server\...\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================

$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@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)


; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SysModel = WMIQuery("Model","Win32_ComputerSystem")
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next

Sleep 5

$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
;If $SysMemory = 0
;  $SysMemory = Memory()
;EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key
 Dim $RegArray, $RegView, $Value,$Product,$Key, $rc, $Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $CDKey = $Product + ': ' + $Key
;            ? $Product + ': ' + $Key				; Wanna get this results in diferent Rows in the MDB Database
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; 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("MonHersteller").value = $MonHersteller
$rs.fields.item("MonitorDescription").value = $MonitorDescription
$rs.fields.item("IP").value = $IP
$rs.fields.item("Uhrzeit").value = $Uhrzeit
$rs.fields.item("MAC").value = $MAC
$rs.fields.item("PRIVILEGIEN").value = $Privilegien
$rs.fields.item("COMPUTERNAME").value = $computername
$rs.fields.item("OSNAME").value = $osname
$rs.fields.item("OSTYPE").value = $OSType
$rs.fields.item("SERVICEPACK").value = $ServicePack
$rs.fields.item("INSTALLDATE").value = $InstallDate
$rs.fields.item("LASTBOOTTIME").value = $LastBoottime
$rs.fields.item("OSSERIAL").value = $OSSerial
$rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
$rs.fields.item("SYSMODEL").value = $SysModel
$rs.fields.item("SYSSERIAL").value = $SysSerial
$rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
$rs.fields.item("CPUSPEED").value = $CPUSpeed
;$rs.fields.item("SYSMEMORY").value = $SysMemory
$rs.fields.item("VIDEOCARD").value = $VideoCard
$rs.fields.item("VIDEORES").value = $VideoRes
;$rs.fields.item("NICCARD").value = $NicCard
$rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
$rs.fields.item("USERNAME").value = $Username
$rs.fields.item("CDKey").value = $CDKey
$rs.update

$rs.Close

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance, $objLocator 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $ = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;;FUNCTION		Memory() 
;; 
;;AUTHOR		Glenn Barnas
;; 
;;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
;; 
;;SYNTAX		Memory([system]) 
;; 
;;VERSION		4.0 
;; 
;;DATE			v1.0 - 2004/02/04
;; 
;;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
;; 
;;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;;
;;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;;			or video adapters and this memory is not reported. This can be adjusted for using
;;			the second example below.
;; 
;;RETURNS		Integer - Available Physical RAM (in Megabytes) 
;; 
;;DEPENDENCIES		None 
;; 
;;TESTED WITH		WinXP, Vista, Win7, Win8
;;			Windows Server 2000, 2003, 2008, 2012
;;			Tested with up to 16G of RAM 
;; 
;;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;;			
;;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;;			; (e.g. 0.5 instead of 512). 
;;			$MSize = 512			; Size of smallest RAM module (DIMM)
;;			
;;			$Mem = Memory()			; get reported memory
;;			$Ma  = $Mem / $MSize		; determine number of modules installed
;;			
;;			; If a fractional module is detected, add another full module
;;			; this accounts for a fractional module used for BIOS cache or video RAM
;;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;;			
;;			; $Mem now holds a value based more closely on installer rather than available RAM
;;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;;			
;; 
;
;Function Memory(Optional $_System, Optional $_fGB)
;
;  Dim $_MemMap				; Physical Memory Map
;  Dim $_Start, $_End, $_Step		; for/next start & step increment
;  Dim $_Sum				; running sum of memory from map
;  Dim $_Idx				; temporary index var
;  Dim $_iMem				; Memory region size index
;  Dim $_Error				; Error placeholder
;
;  ; Insure $_System has "\\System\" format if it is specified 
;  If $_System <> ''
;    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
;  EndIf

;  ; Get the memory value from the registry 
;  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
;  $_MemMap = ReadValue($_Idx, '.Translated')

;  ; Check for invalid read and Return 
;  If Len($_MemMap) = 0 Or @ERROR
;    $Memory = 0				; return 0 Mbytes 
;    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
;    Exit $_Error
;  EndIf

;  ; determine system's O/S type based on architecture
;  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
;  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
;    $_Step  = 40	; Define the step size based on the O/S Architecture
;    $_Start = 33	; Offset where memory descriptor blocks start
;    $_Sum   = 0.0	; no unreported base memory to account for
;    $_iMem  = 10	; Memory size index
;  Else
;    $_Step  = 32	; Define the step size based on the O/S Architecture
;    $_Start = 41	; Offset where memory descriptor blocks start
;    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
;    $_iMem  = 2		; Memory size index
;  EndIf
;  $_End = Len($_MemMap) - 8

;  For $_Idx = $_Start to $_End Step $_Step
;    $_Block = SubStr($_MemMap, $_Idx, $_Step)

;    If SubStr($_Block, $_iMem, 1) > 3
;      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
;    Else
;      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
;                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
;    EndIf
;  Next

;  ; Sum is in Bytes - return the total as megabytes
;  $Memory = CInt($_Sum / 1048576)
;  Exit 0

;EndFunction
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208142 - 2013-12-09 04:14 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi,

now my code i validated by kgen:

 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 0
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = "\\server\...\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================

$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@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)


; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SysModel = WMIQuery("Model","Win32_ComputerSystem")
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next

Sleep 5

$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
If $SysMemory = 0
  $SysMemory = Memory()
EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key
 Dim $RegArray, $RegView, $Value,$Product,$Key, $Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $CDKey = $Product + ': ' + $Key
;            ? $Product + ': ' + $Key				; Wanna get this results in diferent Rows in the MDB Database
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; 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("MonHersteller").value = $MonHersteller
$rs.fields.item("MonitorDescription").value = $MonitorDescription
$rs.fields.item("IP").value = $IP
$rs.fields.item("Uhrzeit").value = $Uhrzeit
$rs.fields.item("MAC").value = $MAC
$rs.fields.item("PRIVILEGIEN").value = $Privilegien
$rs.fields.item("COMPUTERNAME").value = $computername
$rs.fields.item("OSNAME").value = $osname
$rs.fields.item("OSTYPE").value = $OSType
$rs.fields.item("SERVICEPACK").value = $ServicePack
$rs.fields.item("INSTALLDATE").value = $InstallDate
$rs.fields.item("LASTBOOTTIME").value = $LastBoottime
$rs.fields.item("OSSERIAL").value = $OSSerial
$rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
$rs.fields.item("SYSMODEL").value = $SysModel
$rs.fields.item("SYSSERIAL").value = $SysSerial
$rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
$rs.fields.item("CPUSPEED").value = $CPUSpeed
;$rs.fields.item("SYSMEMORY").value = $SysMemory
$rs.fields.item("VIDEOCARD").value = $VideoCard
$rs.fields.item("VIDEORES").value = $VideoRes
;$rs.fields.item("NICCARD").value = $NicCard
$rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
$rs.fields.item("USERNAME").value = $Username
$rs.fields.item("CDKey").value = $CDKey
$rs.update

$rs.Close

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $i, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $i = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;FUNCTION		Memory() 
; 
;AUTHOR		Glenn Barnas
; 
;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
; 
;SYNTAX		Memory([system]) 
; 
;VERSION		4.0 
; 
;DATE			v1.0 - 2004/02/04
; 
;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
; 
;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;
;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;			or video adapters and this memory is not reported. This can be adjusted for using
;			the second example below.
; 
;RETURNS		Integer - Available Physical RAM (in Megabytes) 
; 
;DEPENDENCIES		None 
; 
;TESTED WITH		WinXP, Vista, Win7, Win8
;			Windows Server 2000, 2003, 2008, 2012
;			Tested with up to 16G of RAM 
; 
;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;			
;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;			; (e.g. 0.5 instead of 512). 
;			$MSize = 512			; Size of smallest RAM module (DIMM)
;			
;			$Mem = Memory()			; get reported memory
;			$Ma  = $Mem / $MSize		; determine number of modules installed
;			
;			; If a fractional module is detected, add another full module
;			; this accounts for a fractional module used for BIOS cache or video RAM
;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;			
;			; $Mem now holds a value based more closely on installer rather than available RAM
;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;			
; 

Function Memory(Optional $_System)

  DIM $_Block				;
  Dim $_MemMap				; Physical Memory Map
  Dim $_Start, $_End, $_Step		; for/next start & step increment
  Dim $_Sum				; running sum of memory from map
  Dim $_Idx				; temporary index var
  Dim $_iMem				; Memory region size index
  Dim $_Error				; Error placeholder

  ; Insure $_System has "\\System\" format if it is specified 
  If $_System <> ''
    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
  EndIf
  ; Get the memory value from the registry 
  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
  $_MemMap = ReadValue($_Idx, '.Translated')
  ; Check for invalid read and Return 
  If Len($_MemMap) = 0 Or @ERROR
    $Memory = 0				; return 0 Mbytes 
    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
    Exit $_Error
  EndIf
  ; determine system's O/S type based on architecture
  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
    $_Step  = 40	; Define the step size based on the O/S Architecture
    $_Start = 33	; Offset where memory descriptor blocks start
    $_Sum   = 0.0	; no unreported base memory to account for
    $_iMem  = 10	; Memory size index
  Else
    $_Step  = 32	; Define the step size based on the O/S Architecture
    $_Start = 41	; Offset where memory descriptor blocks start
    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
    $_iMem  = 2		; Memory size index
  EndIf
  $_End = Len($_MemMap) - 8
  For $_Idx = $_Start to $_End Step $_Step
    $_Block = SubStr($_MemMap, $_Idx, $_Step)
    If SubStr($_Block, $_iMem, 1) > 3
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
    Else
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
    EndIf
  Next
  ; Sum is in Bytes - return the total as megabytes
  $Memory = CInt($_Sum / 1048576)
  Exit 0

EndFunction



But i get this error:

 Code:
KGen v2.4 - Kixtart script UDF linker
(C) 2000-2013 - Glenn Barnas / Inno-Tech Consulting

Searching for available UDFs............................
 229 UDFs located in 139 files.
No UDFs needed for this generation!
Generation of inventory.KIX is complete!

Pass 1 : 562
Pass 2 .......

Warning: Undeclared variable.
              Variable Name: $
                In function: Main
         Referenced on line: 81
         Implicit declaration as GLOBAL!
Pass 3 : 562
Pass 4 .......
Pass 5 : 561
 1 warnings generated, 562 lines processed.


what does this mean? the other errors in declaration are fixed.

Top
#208145 - 2013-12-09 04:43 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
OK - let's look this over...

You see that KGen adds this line to the beginning of your script:
 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
This is just a reference of when the code was generated and which version of Kixtart was used. This becomes important if you tokenize your code.. KGen leaves a .GEN file in your folder that is not tokenized, so you never have to worry about losing your script source code.

The next section is the output from KGen. There are never "errors" from KGen, just "Warnings". Due to Kixtart's free-form layout, it's difficult to detect strings and commands that spread across multiple lines.

The KGen output reports finding 229 UDFs in your library and local folder in 139 separate files. (some files are libraries and contain many functions!) It also says that none are needed for the generation of your script. (you manually included the required functions.)

After KGen assembles your script, it makes 5 passes through your code using the Sanity UDF. This identifies and warns about several common issues:
Pass 1 locates and indexes all of the variables
Pass 2 reports on variables that were not declared, or declared but not used
Pass 3 indexes the paired objects
Pass 4 reports on mismatched paired objects
Pass 5 generates the reports

The warning shows that on line 81, you used a variable ($) that was not Dim'd or otherwise declared, and reminds you that it will be treated as a GLOBAL var. Review line 81
 Code:
$ = SetOption('Explicit', 'On')
and decide to either use a different, previously declared variable or define the variable that was used. The function is "Main" - your main code, but would show a function name if the warning occurred inside a function.

Sometimes, it will not be possible to eliminate all warnings. I usually investigate and confirm that it is not an issue. One of the most common is "Variable inside string", which is not recommended, but often occurs in an Execute statement. The coding workaround to avoid KGen errors usually isn't worth the effort for Execute statements. \:\)

In this case, the issue is minor and will not affect the script, but adding a
 Code:
Dim $         ; temp var
to your variable declaration section will eliminate that warning message completely. It's really up to you to

If you take the Memory and WMIQuery UDFs out of your inventory.txt file, KGen should put them back into the completed script. This makes your source file smaller and appear less complex. KGen should then report using 2 UDFs from the library.

Good work getting this organized!

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208156 - 2013-12-10 04:16 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi,

thanks for help. Now is my script organized, but not functional :-)

i see that there is no database connect, no writing rows and the script isnt running into errors. Can you help me how i can detect whats wrong? how can i enable debugging to know whats wrong now.

Backfight

Top
#208159 - 2013-12-10 05:32 PM Re: Asset Inventory to Access Database [Re: backfight]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22344
Loc: OK
 Code:
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"

$cn = CreateObject ("ADODB.Connection")
if @error "issue, #" @error get $ exit @error endif
$cmd = CreateObject ("ADODB.Command")
if @error "issue, #" @error get $ exit @error endif
$rs = CreateObject ("ADODB.RecordSet")
if @error "issue, #" @error get $ exit @error endif

$cn.connectionstring = $CNstring
if @error "issue, #" @error get $ exit @error endif
$cn.Open
if @error "issue, #" @error get $ exit @error endif
$cmd.activeconnection = $cn
if @error "issue, #" @error get $ exit @error endif
$rs.cursortype = 3
if @error "issue, #" @error get $ exit @error endif
$rs.locktype = 3
if @error "issue, #" @error get $ exit @error endif
$rs.activecommand = $cmd
if @error "issue, #" @error get $ exit @error endif

$cmd.commandtext = $CMDtxt 
if @error "issue, #" @error get $ exit @error endif
$rs.Open ($cmd)
if @error "issue, #" @error get $ exit @error endif
_________________________
!

download KiXnet

Top
#208160 - 2013-12-10 05:36 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
I assume you masked this line when posting.. have you defined it properly in your prod script?
 Code:
$DBpath = "\\server\...\IT-Inventar.mdb"
These lines
 Code:
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"
will no longer work with the NoVarsInStrings and NoMacrosInStrings settings. Seach the core code for any variables or macros inside quotes and change them to the following format:
 Code:
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + $DBpath
$CMDtxt = "select * from COMPUTERS where computername = '" + @WKSTA +"'"
You will need to search the remaining code for this kind of issue and resolve it. You should not have to check the UDFs as it is a requirement of all public UDFs to properly support these options.

As for debugging, the simplest is something like:
 Code:
XXXXX - @SERROR @CRLF
where the "XXXXX" is something to identify the location in the code. It can be a line number, a function or process name (such as "OpenDbObject"). It's quick and easy to add, but you need to remove or comment them in your production code. I use the fMsg() UDF for my debugging in production scripts.. Something like this:
 Code:
$Rtn = SomeFunction()  ; need to check results..
$Msg = 'Call to SomeFunction() returned ' + $Rtn + '; ' + @SERROR
fMsg($Msg, '', 0, 4)
This will display the result returned as well as the error message produced by the function call. You must define $MSG_LOG_ with the path to the log file/name. With this format, the same message (in $Msg) will be written to the screen and the log file. the empty arg ('') allows a different message to be written to the log, usually with more detail. The third arg will suppress the CRLF, allowing multiple calls to "stack" messages. The fourth arg controls the output - a "4" outputs the mesaage only if the Global $DEBUG variable is non-zero.

Using this method and function, I can sprinkle the code with debug messages, set $DEBUG=1, and get lots of information about the progress of the script. When it's working as desired, I set $DEBUG=0 and the messages go away.

I'm not sure about any of your DB command syntax - it looks not so correct, but I'm not a wiz with DB commands. I'd comment those out and see if the command generates the data you are looking for first. Once you know the data has been correctly gathered, you can focus on writing to the DB. I'd also take a good look at the Database Library instead of manually coding the DB comands, especially if you don't have a strong comfort with OOP and writing connection strings. I found the DBlib collection easy enough for me to write a fairly sophisticated inventory system with a SQL back-end.. \:\) I just had to deal with arrays, field names, and some UDF calls.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208161 - 2013-12-10 06:00 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Run this - $DEBUG is true, and I modified the code to bypass all DB commands when in debug mode. The section where the DB write was performed has been uupdated to display the collected data on the screen. You'll see that I commented out the commands from line 217-219 as they are not correct.. the WMIQuery returns an array that you are not handling.

Further, I eliminated the 5-second sleep command. I have no idea why it's there.. this code is already agonizingly slow! (See my earlier recommendation against multiple WMIQuery calls!!) It takes nearly 9 seconds to complete on a quad-core i7 system. WMYSysInfo calls are MUCH less than that - often under 1 second. Also, WMISysInfo returns a single array of values - you don't have to do any further processing.

If you continue to use WMIQuery, read the header and review the examples, then update your code accordingly to properly assign the returned value to your variable. If you can't print to the screen, you won't be able to write to the database.

Finally, the first lines that print show that the lines I referred to earlier are indeed incorrect and contain the variable and macro names rather than the contents. You won't be able to open the DB unless you update these.

Glenn
 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

Dim $
DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 1
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = "\\server\...\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"
'CNstring: ' $CNstring ?
'  CMDtxt: ' $CMDtxt ?

If Not $DEBUG
  $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)
EndIf

; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SysModel = WMIQuery("Model","Win32_ComputerSystem")
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next

; Sleep 5   ; Why?   WHY???? 

$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
If $SysMemory = 0
  $SysMemory = Memory()
EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key
 Dim $RegArray, $RegView, $Value,$Product,$Key, $Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $CDKey = $Product + ': ' + $Key
;            ? $Product + ': ' + $Key				; Wanna get this results in diferent Rows in the MDB Database
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; ADD RECORDS TO THE DATABASE
; ======================================================================

If $DEBUG
 
  'MonHersteller: ' $MonHersteller ?
  'MonitorDescription: ' $MonitorDescription ?
  'IP: ' $IP ?
  'Uhrzeit: ' $Uhrzeit ?
  'MAC: ' $MAC ?
  'PRIVILEGIEN: ' $Privilegien ?
  'COMPUTERNAME: ' $computername ?
  'OSNAME: ' ? ; $osname ?
  'OSTYPE: ' ? ; $OSType ?
  'SERVICEPACK: ' $ServicePack ?
  'INSTALLDATE: ' $InstallDate ?
  'LASTBOOTTIME: ' $LastBoottime ?
  'OSSERIAL: ' $OSSerial ?
  'SYSMANUFACTURER: ' $SysManufacturer ?
  'SYSMODEL: ' $SysModel ?
  'SYSSERIAL: ' $SysSerial ?
  'CPUDESCRIPTION: ' $CPUDescription ?
  'CPUSPEED: ' $CPUSpeed ?
  ;'SYSMEMORY: ' $SysMemory ?
  'VIDEOCARD: ' $VideoCard ?
  'VIDEORES: ' $VideoRes ?
  ;'NICCARD: ' $NicCard ?
  'MODIFYDATETIME: ' $ModifyDateTime ?
  'USERNAME: ' $Username ?
  'CDKey: ' $CDKey ?

Else

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

 $rs.fields.item("MonHersteller").value = $MonHersteller
 $rs.fields.item("MonitorDescription").value = $MonitorDescription
 $rs.fields.item("IP").value = $IP
 $rs.fields.item("Uhrzeit").value = $Uhrzeit
 $rs.fields.item("MAC").value = $MAC
 $rs.fields.item("PRIVILEGIEN").value = $Privilegien
 $rs.fields.item("COMPUTERNAME").value = $computername
 $rs.fields.item("OSNAME").value = $osname
 $rs.fields.item("OSTYPE").value = $OSType
 $rs.fields.item("SERVICEPACK").value = $ServicePack
 $rs.fields.item("INSTALLDATE").value = $InstallDate
 $rs.fields.item("LASTBOOTTIME").value = $LastBoottime
 $rs.fields.item("OSSERIAL").value = $OSSerial
 $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
 $rs.fields.item("SYSMODEL").value = $SysModel
 $rs.fields.item("SYSSERIAL").value = $SysSerial
 $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
 $rs.fields.item("CPUSPEED").value = $CPUSpeed
 ;$rs.fields.item("SYSMEMORY").value = $SysMemory
 $rs.fields.item("VIDEOCARD").value = $VideoCard
 $rs.fields.item("VIDEORES").value = $VideoRes
 ;$rs.fields.item("NICCARD").value = $NicCard
 $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
 $rs.fields.item("USERNAME").value = $Username
 $rs.fields.item("CDKey").value = $CDKey
 $rs.update
 $rs.Close

EndIf

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $i, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $i = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;FUNCTION		Memory() 
; 
;AUTHOR		Glenn Barnas
; 
;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
; 
;SYNTAX		Memory([system]) 
; 
;VERSION		4.0 
; 
;DATE			v1.0 - 2004/02/04
; 
;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
; 
;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;
;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;			or video adapters and this memory is not reported. This can be adjusted for using
;			the second example below.
; 
;RETURNS		Integer - Available Physical RAM (in Megabytes) 
; 
;DEPENDENCIES		None 
; 
;TESTED WITH		WinXP, Vista, Win7, Win8
;			Windows Server 2000, 2003, 2008, 2012
;			Tested with up to 16G of RAM 
; 
;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;			
;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;			; (e.g. 0.5 instead of 512). 
;			$MSize = 512			; Size of smallest RAM module (DIMM)
;			
;			$Mem = Memory()			; get reported memory
;			$Ma  = $Mem / $MSize		; determine number of modules installed
;			
;			; If a fractional module is detected, add another full module
;			; this accounts for a fractional module used for BIOS cache or video RAM
;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;			
;			; $Mem now holds a value based more closely on installer rather than available RAM
;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;			
; 

Function Memory(Optional $_System)

  DIM $_Block				;
  Dim $_MemMap				; Physical Memory Map
  Dim $_Start, $_End, $_Step		; for/next start & step increment
  Dim $_Sum				; running sum of memory from map
  Dim $_Idx				; temporary index var
  Dim $_iMem				; Memory region size index
  Dim $_Error				; Error placeholder

  ; Insure $_System has "\\System\" format if it is specified 
  If $_System <> ''
    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
  EndIf
  ; Get the memory value from the registry 
  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
  $_MemMap = ReadValue($_Idx, '.Translated')
  ; Check for invalid read and Return 
  If Len($_MemMap) = 0 Or @ERROR
    $Memory = 0				; return 0 Mbytes 
    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
    Exit $_Error
  EndIf
  ; determine system's O/S type based on architecture
  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
    $_Step  = 40	; Define the step size based on the O/S Architecture
    $_Start = 33	; Offset where memory descriptor blocks start
    $_Sum   = 0.0	; no unreported base memory to account for
    $_iMem  = 10	; Memory size index
  Else
    $_Step  = 32	; Define the step size based on the O/S Architecture
    $_Start = 41	; Offset where memory descriptor blocks start
    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
    $_iMem  = 2		; Memory size index
  EndIf
  $_End = Len($_MemMap) - 8
  For $_Idx = $_Start to $_End Step $_Step
    $_Block = SubStr($_MemMap, $_Idx, $_Step)
    If SubStr($_Block, $_iMem, 1) > 3
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
    Else
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
    EndIf
  Next
  ; Sum is in Bytes - return the total as megabytes
  $Memory = CInt($_Sum / 1048576)
  Exit 0

EndFunction
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208162 - 2013-12-10 06:35 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
By preventing the errors from WMIQuery(), I get the following results. Since I just prevented the errors by converting the array returned to a normal (non-array) variable, I can't guarantee the data is correct, but it no longer fails. You will still need to review the values returned from the WMIQuery calls.

I uncommented the message output of the product/CD Key processing to illustrate my earlier comment that only the last product is being logged.. You will need to create an array using something like this before the process loop:
 Code:
 Dim $aProducts  ; array of product data in prod,Key format
Dim $P   ; array pointer
$P = -1
Inside the current code, replace the "$CDKey = $Product + ': ' + $Key" line with the following:
 Code:
$P = $P + 1   ; increase array pointer
ReDim Preserve $aData[$P]    ; increase the array size, preserving prior data
$aData[$P] = $Product + ',' + $Key
You will now have an array of Product,Key pairs to work with. Note that the output below lists 4 products/keys found but only the last is in your report.

I also added code to show the processing time, after removing all of the Sleep commands and excluding the screen output to give you an idea of the WMIQuery performance.
 Code:
Asset Audit Script Processing...

  DBPath: \\server\...\IT-Inventar.mdb
CNstring: Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath
  CMDtxt: select * from COMPUTERS where computername = '@WKSTA'

Found Windows 8 Pro: PPC4M-WWWWW-XXXXX-YYYYY-ZZZZZ
Found Microsoft Office Professional Plus 2013: Q29K7-WWWWW-XXXXX-YYYYY-ZZZZZ
Found Microsoft Project Professional 2013: VB7FH-WWWWW-XXXXX-YYYYY-ZZZZZ
Found Microsoft Visio Professional 2013: M7FK3-WWWWW-XXXXX-YYYYY-ZZZZZ

Processing time: 15.325 seconds

MonHersteller: Samsung
MonitorDescription: SyncMaster 245T(Digital)
IP: 172. 16. 12. 16
Uhrzeit: 12:20:50
MAC:
PRIVILEGIEN: USER
COMPUTERNAME: ICWD011
OSNAME: Microsoft Windows 8 Pro
OSTYPE: Multiprocessor Free
SERVICEPACK:
INSTALLDATE: 20130105233330.000000-300
LASTBOOTTIME: 2013/12/10
OSSERIAL: 00178-10077-75869-AA763
SYSMANUFACTURER: Hewlett-Packard
SYSMODEL: HP Compaq 8100 Elite CMT PC
SYSSERIAL: 2UA04305JS
CPUDESCRIPTION: Intel(R) Core(TM) i7 CPU         860  @ 2.80GHz
CPUSPEED: 2933
VIDEOCARD: AMD RADEON HD 6450
VIDEORES: 1920 x 1200 x 4294967296 colors
MODIFYDATETIME: 2013/12/10 12:20:51
USERNAME: gbarnas
CDKey: Microsoft Visio Professional 2013: M7FK3-WWWWW-XXXXX-YYYYY-ZZZZZ

(ICWD011) - K:\ITCG\misc>


Edited by Glenn Barnas (2014-01-02 04:09 PM)
Edit Reason: fixed bad code tag
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208172 - 2013-12-16 11:54 AM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi Glenn,
thx for your big support! But how do you get these results? can you show me the code witch shows the results above?

i have changed my code with your help from the three post bevore, but i dont get these results.

Top
#208175 - 2013-12-18 01:00 AM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Honeymooning this week, so little time to post, but I will tell you that the WMIQuery returns an ARRAY, not a simple VALUE. Instead of

$Var = WMIQuery(blah-blah)

try

$Var = WMIQuery(blah-blah0)[0]

This will return the first element of the array that the function returns. It's simple, but not always accurate, as the query could return multiple values. You'll need to determine where that's appropriate.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208245 - 2014-01-02 02:21 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Happy New Year everybody!

sorry but i get an error.
My Code:

 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

Dim $
DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath
Dim $aProducts 			 	; array of product data in prod,Key format
DIM $Product				;
Dim $P   					; array pointer
DIM $Key					; CDKeys	
DIM $aData					;


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 1
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = ".....\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"
'CNstring: ' $CNstring ?
'  CMDtxt: ' $CMDtxt ?

If Not $DEBUG
  $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)
EndIf

; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")[0]
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")[0]
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")[0]
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")[0]
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")[0]
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")[0]
$SysModel = WMIQuery("Model","Win32_ComputerSystem")[0]
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")[0]
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next
$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")[0]
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
If $SysMemory = 0
  $SysMemory = Memory()
EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")[0]
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")[0]
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key
$P = -1
ReDim Preserve $aData[$P]    ; increase the array size, preserving prior data
$aData[$P] = $Product + ',' + $Key

 Dim $RegArray, $RegView, $Value,$Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $P = $P + 1  ; increase array pointer
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; ADD RECORDS TO THE DATABASE
; ======================================================================

If $DEBUG
 
  'MonHersteller: ' $MonHersteller ?
  'MonitorDescription: ' $MonitorDescription ?
  'IP: ' $IP ?
  'Uhrzeit: ' $Uhrzeit ?
  'MAC: ' $MAC ?
  'PRIVILEGIEN: ' $Privilegien ?
  'COMPUTERNAME: ' $computername ?
  'OSNAME: ' $osname ?
  'OSTYPE: ' $OSType ?
  'SERVICEPACK: ' $ServicePack ?
  'INSTALLDATE: ' $InstallDate ?
  'LASTBOOTTIME: ' $LastBoottime ?
  'OSSERIAL: ' $OSSerial ?
  'SYSMANUFACTURER: ' $SysManufacturer ?
  'SYSMODEL: ' $SysModel ?
  'SYSSERIAL: ' $SysSerial ?
  'CPUDESCRIPTION: ' $CPUDescription ?
  'CPUSPEED: ' $CPUSpeed ?
  ;'SYSMEMORY: ' $SysMemory ?
  'VIDEOCARD: ' $VideoCard ?
  'VIDEORES: ' $VideoRes ?
  ;'NICCARD: ' $NicCard ?
  'MODIFYDATETIME: ' $ModifyDateTime ?
  'USERNAME: ' $Username ?
  'CDKey: ' $CDKey ?

Else

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

 $rs.fields.item("MonHersteller").value = $MonHersteller
 $rs.fields.item("MonitorDescription").value = $MonitorDescription
 $rs.fields.item("IP").value = $IP
 $rs.fields.item("Uhrzeit").value = $Uhrzeit
 $rs.fields.item("MAC").value = $MAC
 $rs.fields.item("PRIVILEGIEN").value = $Privilegien
 $rs.fields.item("COMPUTERNAME").value = $computername
 $rs.fields.item("OSNAME").value = $osname
 $rs.fields.item("OSTYPE").value = $OSType
 $rs.fields.item("SERVICEPACK").value = $ServicePack
 $rs.fields.item("INSTALLDATE").value = $InstallDate
 $rs.fields.item("LASTBOOTTIME").value = $LastBoottime
 $rs.fields.item("OSSERIAL").value = $OSSerial
 $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
 $rs.fields.item("SYSMODEL").value = $SysModel
 $rs.fields.item("SYSSERIAL").value = $SysSerial
 $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
 $rs.fields.item("CPUSPEED").value = $CPUSpeed
 ;$rs.fields.item("SYSMEMORY").value = $SysMemory
 $rs.fields.item("VIDEOCARD").value = $VideoCard
 $rs.fields.item("VIDEORES").value = $VideoRes
 ;$rs.fields.item("NICCARD").value = $NicCard
 $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
 $rs.fields.item("USERNAME").value = $Username
 $rs.fields.item("CDKey").value = $CDKey
 $rs.update
 $rs.Close

EndIf

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $i, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $i = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;FUNCTION		Memory() 
; 
;AUTHOR		Glenn Barnas
; 
;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
; 
;SYNTAX		Memory([system]) 
; 
;VERSION		4.0 
; 
;DATE			v1.0 - 2004/02/04
; 
;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
; 
;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;
;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;			or video adapters and this memory is not reported. This can be adjusted for using
;			the second example below.
; 
;RETURNS		Integer - Available Physical RAM (in Megabytes) 
; 
;DEPENDENCIES		None 
; 
;TESTED WITH		WinXP, Vista, Win7, Win8
;			Windows Server 2000, 2003, 2008, 2012
;			Tested with up to 16G of RAM 
; 
;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;			
;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;			; (e.g. 0.5 instead of 512). 
;			$MSize = 512			; Size of smallest RAM module (DIMM)
;			
;			$Mem = Memory()			; get reported memory
;			$Ma  = $Mem / $MSize		; determine number of modules installed
;			
;			; If a fractional module is detected, add another full module
;			; this accounts for a fractional module used for BIOS cache or video RAM
;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;			
;			; $Mem now holds a value based more closely on installer rather than available RAM
;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;			
; 

Function Memory(Optional $_System)

  DIM $_Block				;
  Dim $_MemMap				; Physical Memory Map
  Dim $_Start, $_End, $_Step		; for/next start & step increment
  Dim $_Sum				; running sum of memory from map
  Dim $_Idx				; temporary index var
  Dim $_iMem				; Memory region size index
  Dim $_Error				; Error placeholder

  ; Insure $_System has "\\System\" format if it is specified 
  If $_System <> ''
    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
  EndIf
  ; Get the memory value from the registry 
  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
  $_MemMap = ReadValue($_Idx, '.Translated')
  ; Check for invalid read and Return 
  If Len($_MemMap) = 0 Or @ERROR
    $Memory = 0				; return 0 Mbytes 
    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
    Exit $_Error
  EndIf
  ; determine system's O/S type based on architecture
  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
    $_Step  = 40	; Define the step size based on the O/S Architecture
    $_Start = 33	; Offset where memory descriptor blocks start
    $_Sum   = 0.0	; no unreported base memory to account for
    $_iMem  = 10	; Memory size index
  Else
    $_Step  = 32	; Define the step size based on the O/S Architecture
    $_Start = 41	; Offset where memory descriptor blocks start
    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
    $_iMem  = 2		; Memory size index
  EndIf
  $_End = Len($_MemMap) - 8
  For $_Idx = $_Start to $_End Step $_Step
    $_Block = SubStr($_MemMap, $_Idx, $_Step)
    If SubStr($_Block, $_iMem, 1) > 3
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
    Else
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
    EndIf
  Next
  ; Sum is in Bytes - return the total as megabytes
  $Memory = CInt($_Sum / 1048576)
  Exit 0

EndFunction


Error:
 Code:
ERROR : array reference out of bounds!
Script: C:\....\inventory.kix
Line  : 176


hope ive modified my code as you expected.

backfight

Top
#208246 - 2014-01-02 03:00 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
This isn't valid - you're trying to define an array with no elements (different than an array with zero elements).
 Code:
; Get CD Key
$P = -1
ReDim Preserve $aData[$P]    ; increase the array size, preserving prior data
$aData[$P] = $Product + ',' + $Key
The way this type of thing works is like this:
 Code:
Dim $aData  ; declare a plain variable
Dim $P   ; declare the pointer
$P = -1  ; pointer is "empty", since the first position is "0"

; Enumerate some data
For Each $Thing in $Collection
  If InStr($Thing, 'Need')  ; does this thing contain what we're looking for?
    ; Add the object to an array of matches
    $P = $P + 1  ; increment the pointer to the next array position
    ReDim Preserve $aData[$P] ; increase the array, preserving prior contents
    $aData[$P] = $Thing
  EndIf
Next
You'll need to use this model in your code. Note that the increment of $P and ReDim of the array only occur when a match is found. This is where you need to put the array redim and assignment:
 Code:
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $P = $P + 1  ; increase array pointer
        EndIf


Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208247 - 2014-01-02 03:27 PM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
hi,

sorry but im a little depressed. i try and try. but i dont get the debug results that you get.

i changed my code in this way:

 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

Dim $
DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath
Dim $aProducts 			 	; array of product data in prod,Key format
DIM $Product				;
Dim $P   					; array pointer
DIM $Key					; CDKeys	
DIM $aData					;


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 1
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = ".....\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"
'CNstring: ' $CNstring ?
'  CMDtxt: ' $CMDtxt ?

If Not $DEBUG
  $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)
EndIf

; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")[0]
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")[0]
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")[0]
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")[0]
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")[0]
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")[0]
$SysModel = WMIQuery("Model","Win32_ComputerSystem")[0]
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")[0]
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next
$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")[0]
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
If $SysMemory = 0
  $SysMemory = Memory()
EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")[0]
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")[0]
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key

$P = -1  ; pointer is "empty", since the first position is "0"

 Dim $RegArray, $RegView, $Value,$Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $P = $P + 1  ; increase array pointer
		  ReDim Preserve $aData[$P] ; increase the array, preserving prior contents
			$aData[$P] = $CDKey
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; ADD RECORDS TO THE DATABASE
; ======================================================================

If $DEBUG
 
  'MonHersteller: ' $MonHersteller ?
  'MonitorDescription: ' $MonitorDescription ?
  'IP: ' $IP ?
  'Uhrzeit: ' $Uhrzeit ?
  'MAC: ' $MAC ?
  'PRIVILEGIEN: ' $Privilegien ?
  'COMPUTERNAME: ' $computername ?
  'OSNAME: ' $osname ?
  'OSTYPE: ' $OSType ?
  'SERVICEPACK: ' $ServicePack ?
  'INSTALLDATE: ' $InstallDate ?
  'LASTBOOTTIME: ' $LastBoottime ?
  'OSSERIAL: ' $OSSerial ?
  'SYSMANUFACTURER: ' $SysManufacturer ?
  'SYSMODEL: ' $SysModel ?
  'SYSSERIAL: ' $SysSerial ?
  'CPUDESCRIPTION: ' $CPUDescription ?
  'CPUSPEED: ' $CPUSpeed ?
  ;'SYSMEMORY: ' $SysMemory ?
  'VIDEOCARD: ' $VideoCard ?
  'VIDEORES: ' $VideoRes ?
  ;'NICCARD: ' $NicCard ?
  'MODIFYDATETIME: ' $ModifyDateTime ?
  'USERNAME: ' $Username ?
  'CDKey: ' $CDKey ?

Else

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

 $rs.fields.item("MonHersteller").value = $MonHersteller
 $rs.fields.item("MonitorDescription").value = $MonitorDescription
 $rs.fields.item("IP").value = $IP
 $rs.fields.item("Uhrzeit").value = $Uhrzeit
 $rs.fields.item("MAC").value = $MAC
 $rs.fields.item("PRIVILEGIEN").value = $Privilegien
 $rs.fields.item("COMPUTERNAME").value = $computername
 $rs.fields.item("OSNAME").value = $osname
 $rs.fields.item("OSTYPE").value = $OSType
 $rs.fields.item("SERVICEPACK").value = $ServicePack
 $rs.fields.item("INSTALLDATE").value = $InstallDate
 $rs.fields.item("LASTBOOTTIME").value = $LastBoottime
 $rs.fields.item("OSSERIAL").value = $OSSerial
 $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
 $rs.fields.item("SYSMODEL").value = $SysModel
 $rs.fields.item("SYSSERIAL").value = $SysSerial
 $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
 $rs.fields.item("CPUSPEED").value = $CPUSpeed
 ;$rs.fields.item("SYSMEMORY").value = $SysMemory
 $rs.fields.item("VIDEOCARD").value = $VideoCard
 $rs.fields.item("VIDEORES").value = $VideoRes
 ;$rs.fields.item("NICCARD").value = $NicCard
 $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
 $rs.fields.item("USERNAME").value = $Username
 $rs.fields.item("CDKey").value = $CDKey
 $rs.update
 $rs.Close

EndIf

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $i, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $i = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;FUNCTION		Memory() 
; 
;AUTHOR		Glenn Barnas
; 
;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
; 
;SYNTAX		Memory([system]) 
; 
;VERSION		4.0 
; 
;DATE			v1.0 - 2004/02/04
; 
;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
; 
;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;
;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;			or video adapters and this memory is not reported. This can be adjusted for using
;			the second example below.
; 
;RETURNS		Integer - Available Physical RAM (in Megabytes) 
; 
;DEPENDENCIES		None 
; 
;TESTED WITH		WinXP, Vista, Win7, Win8
;			Windows Server 2000, 2003, 2008, 2012
;			Tested with up to 16G of RAM 
; 
;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;			
;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;			; (e.g. 0.5 instead of 512). 
;			$MSize = 512			; Size of smallest RAM module (DIMM)
;			
;			$Mem = Memory()			; get reported memory
;			$Ma  = $Mem / $MSize		; determine number of modules installed
;			
;			; If a fractional module is detected, add another full module
;			; this accounts for a fractional module used for BIOS cache or video RAM
;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;			
;			; $Mem now holds a value based more closely on installer rather than available RAM
;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;			
; 

Function Memory(Optional $_System)

  DIM $_Block				;
  Dim $_MemMap				; Physical Memory Map
  Dim $_Start, $_End, $_Step		; for/next start & step increment
  Dim $_Sum				; running sum of memory from map
  Dim $_Idx				; temporary index var
  Dim $_iMem				; Memory region size index
  Dim $_Error				; Error placeholder

  ; Insure $_System has "\\System\" format if it is specified 
  If $_System <> ''
    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
  EndIf
  ; Get the memory value from the registry 
  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
  $_MemMap = ReadValue($_Idx, '.Translated')
  ; Check for invalid read and Return 
  If Len($_MemMap) = 0 Or @ERROR
    $Memory = 0				; return 0 Mbytes 
    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
    Exit $_Error
  EndIf
  ; determine system's O/S type based on architecture
  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
    $_Step  = 40	; Define the step size based on the O/S Architecture
    $_Start = 33	; Offset where memory descriptor blocks start
    $_Sum   = 0.0	; no unreported base memory to account for
    $_iMem  = 10	; Memory size index
  Else
    $_Step  = 32	; Define the step size based on the O/S Architecture
    $_Start = 41	; Offset where memory descriptor blocks start
    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
    $_iMem  = 2		; Memory size index
  EndIf
  $_End = Len($_MemMap) - 8
  For $_Idx = $_Start to $_End Step $_Step
    $_Block = SubStr($_MemMap, $_Idx, $_Step)
    If SubStr($_Block, $_iMem, 1) > 3
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
    Else
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
    EndIf
  Next
  ; Sum is in Bytes - return the total as megabytes
  $Memory = CInt($_Sum / 1048576)
  Exit 0

EndFunction


and my result isnt an cd key...its empty :-(

Results:

 Code:
CNstring: Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath
  CMDtxt: select * from COMPUTERS where computername = '@WKSTA'
MonHersteller: (Standardmonitortypen)
MonitorDescription: PnP-Monitor (Standard)
IP: ...
Uhrzeit: 15:19:38
MAC: ...
PRIVILEGIEN: ....
COMPUTERNAME: ...
OSNAME: Microsoft Windows 8 Pro
OSTYPE: Multiprocessor Free
SERVICEPACK:
INSTALLDATE: 20131118153103.000000+060
LASTBOOTTIME: 2014/01/02
OSSERIAL: 00178-10927-61599-AA949
SYSMANUFACTURER: Acer
SYSMODEL: Veriton M290
SYSSERIAL: ....
CPUDESCRIPTION: Intel(R) Pentium(R) CPU G630 @ 2.70GHz
CPUSPEED: 2700
VIDEOCARD: Intel(R) HD Graphics
VIDEORES: 1920 x 1080 x 4294967296 Farben
MODIFYDATETIME: 2014/01/02 15:19:38
USERNAME: ....
CDKey:


and i dont get the list of keys like you get. :-/

Top
#208248 - 2014-01-02 04:15 PM Re: Asset Inventory to Access Database [Re: backfight]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Re-read the third post from the end of the prior page. The code tag was not correct and I see how the result could have been confusing. I fixed the tag so the two code sections now appear correctly.

Once you have a set of keys, you'll need to enumerate them with something like
 Code:
'CD Keys: ' ?
For Each $Key in $aData
  $Key ?
Next
You need to change the code to properly build an array of CD Keys, and then modify the output to display the keys. Here's a cool shortcut to display the contents of a small array -
 Code:
$Array = 'Reg', 'Green', 'Blue'
Join($Array, ', ') ?
This is great for debugging.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#208251 - 2014-01-03 08:55 AM Re: Asset Inventory to Access Database [Re: Glenn Barnas]
backfight Offline
Getting the hang of it

Registered: 2007-01-22
Posts: 61
Loc: Germany
Hi,
now it works. Thanks!
can you help me to get "NIC" and your UDF Memory value to work?
after i have the right results in debug i wanna learn how to deal with the database and arrays

my code:
 Code:
;; KixGenerated: 2013/12/09 16:08:52 / Kix32 Version 4.62
; Hardware Inventory , OS Information & MS Key Inventory - Logon Script lists all Hardware & MS CD Keys per Client
; 
; Version 0.1 - Backfight - 09.12.2013
; UDFs: UDF WMIQuery, UDF MEMORY, (DBLib.udf)
;
; Tasks:
; Identify Computer (@WKSTA)
;
; Query database
;  is this computer listed?
;    If so, was the last update more than 6 months ago?
;      If NOT, exit - nothing to do
;
; Need to perform an inventory
;
; Clear/Init the NEWDATA array
;
; Query for the following items and load the NEWDATA array with the results. 
; O/S Version
; Hardware Platform
; Installed Software  
;


; DESCRIPTION OF THE SCRIPT
;=======================================================================
; Collect all Hardware & OS Informationen from all WKST in the Domain to get a complete Inventory + all installed Microsoft Keys


Break On


; Declare variables
; ======================================================================

Dim $
DIM $CNstring				; Database variables
DIM $CMDtxt					; 
DIM $cmd					; 
DIM $rs					; 
DIM $cn					;

DIM $ModifyDateTime			; Return ModifyDateTime 
DIM $computername				; Workstation Name
DIM $username				; Username
DIM $osname					; OS Name
DIM $ServicePack				; SP
DIM $InstallDate				; Install Date
DIM $LastBoottime				; Last Boottime
DIM $OSSerial				; OS SN
DIM $SysManufacturer			; System Manufacturer
DIM $SysModel				; System Model
DIM $SysSerial				; System SN
DIM $CPUDescription			; CPU Description
DIM $CPUSpeed				; CPU Speed
DIM $SysMemory				; System RAM
DIM $VideoCard				; Video Card Name
DIM $VideoRes				; Video Resulution
;DIM $NicCard				; Networkcard
;DIM $nic					; NIC Var.
DIM $CDKey					; CDKeys (OS Key, MS Office, etc)
DIM $Privilegien				; User Privilegien
DIM $IP					; IP Adress
DIM $MAC					; Mac Hardware Adress
DIM $Uhrzeit				; Time
DIM $MonitorDescription			; Monitor Description
DIM $MonHersteller			; Monitor Manufactor
DIM $OSType					; Get OSType
DIM $wmiColl
DIM $wmiObj
DIM $DBpath
Dim $aProducts 			 	; array of product data in prod,Key format
DIM $Product				;
Dim $P   					; array pointer
DIM $Key					; CDKeys	
DIM $aData					;


Global $DEBUG				; Debug flag
Global $VERSION				; version string
;Global $MSG_LOG_, $ERR_LOG_		; log filenames - used by fMsg()


; Set program options
; ======================================================================
$ = SetOption('Explicit', 'On')
$ = SetOption('NoVarsInStrings', 'On')
$ = SetOption('NoMacrosInStrings', 'On')


; Define values
; ======================================================================
$DEBUG = 1
$VERSION = '1.0'

; CONFIGURE DATABASE PATH
; ======================================================================

$DBpath = "..........\IT-Inventar.mdb"

CLS
AT (1,1) "Asset Audit Script Processing..."
SLEEP 2
CLS

; CONFIGURE DATABASE CONNECTION STRING
; ======================================================================
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBpath"
$CMDtxt = "select * from COMPUTERS where computername = '@WKSTA'"
'CNstring: ' $CNstring ?
'  CMDtxt: ' $CMDtxt ?

If Not $DEBUG
  $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)
EndIf

; MAIN Code
; ======================================================================



; COLLECT WORKSTAION ASSET INFORMATION
; ======================================================================
$MAC = @ADDRESS
$Uhrzeit = @time
$Privilegien = @PRIV
$IP = @IPADDRESS0
$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$osname = WMIQuery("Caption","Win32_OperatingSystem")[0]
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")[0]
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")[0]
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")[0]
$LastBoottime = @date
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")[0]
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")[0]
$SysModel = WMIQuery("Model","Win32_ComputerSystem")[0]
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")[0]
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_Processor ")
For Each $wmiObj in $wmiColl
  $CPUDescription = $wmiObj.Name	
Next
$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")[0]
;$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
If $SysMemory = 0
  $SysMemory = Memory()
EndIf
$VideoCard = WMIQuery("Description","Win32_VideoController")[0]
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")[0]
;for each $nic in Split(WMIQuery("ProductName","Win32_NetworkAdapter"),"|")
;if instr($nic,"miniport")=0 and instr($nic,"RAS")=0 and instr($nic,"Parallel")=0
;$NicCard = $nic
$wmiColl = GetObject("WinMgmts:root/cimv2").ExecQuery("Select * FROM Win32_DesktopMonitor")

For Each $wmiObj in $wmiColl
  $MonitorDescription = $wmiObj.Name
  $MonHersteller = $wmiObj.MonitorManufacturer
Next


; Get CD Key

$P = -1  ; pointer is "empty", since the first position is "0"

 Dim $RegArray, $RegView, $Value,$Array2,$guid
  $RegView=setoption("WOW64AlternateRegView","On")
  $RegArray = SearchReg("HKLM\Software\Microsoft","DigitalProductID",2)
  if @onwow64
    $Array2 = SearchReg("HKLM\Software\WOW6432NODE\Microsoft","DigitalProductID",2)
    $RegArray=ArrayAdd($RegArray,$Array2)
  endif
  If ubound($RegArray)<0
      ? 'No matching items found'
  Else
    For Each $Value In $RegArray
      If $Value
        $Product = ReadValue(Join(Split($value,'<=>DigitalProductId'),''),'ProductName')
        If $product=""
          $guid="{" + split(split($value,"{")[1],"}")[0] + "}"
          if instr($value,"WOW6432Node")
            $product=readvalue("HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          else
            $product=readvalue("HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\" + $guid,"DisplayName")
          endif
        endif
        If $Product
          $Key = Get_Product_Key(ReadValue(Join(Split($value,'<=>DigitalProductId'),''), 'DigitalProductID'))
		  $P = $P + 1  ; increase array pointer
		  ReDim Preserve $aData[$P] ; increase the array, preserving prior contents
			$aData[$P] = $product + ': ' + $Key 
        EndIf
      EndIf
    Next
  EndIf
  $RegView=setoption("WOW64AlternateRegView",$RegView)



; ADD RECORDS TO THE DATABASE
; ======================================================================

If $DEBUG
 
  'MonHersteller: ' $MonHersteller ?
  'MonitorDescription: ' $MonitorDescription ?
  'IP: ' $IP ?
  'Uhrzeit: ' $Uhrzeit ?
  'MAC: ' $MAC ?
  'PRIVILEGIEN: ' $Privilegien ?
  'COMPUTERNAME: ' $computername ?
  'OSNAME: ' $osname ?
  'OSTYPE: ' $OSType ?
  'SERVICEPACK: ' $ServicePack ?
  'INSTALLDATE: ' $InstallDate ?
  'LASTBOOTTIME: ' $LastBoottime ?
  'OSSERIAL: ' $OSSerial ?
  'SYSMANUFACTURER: ' $SysManufacturer ?
  'SYSMODEL: ' $SysModel ?
  'SYSSERIAL: ' $SysSerial ?
  'CPUDESCRIPTION: ' $CPUDescription ?
  'CPUSPEED: ' $CPUSpeed ?
;  'SYSMEMORY: ' $SysMemory ?
  'VIDEOCARD: ' $VideoCard ?
  'VIDEORES: ' $VideoRes ?
;  'NICCARD: ' $NicCard ?
  'MODIFYDATETIME: ' $ModifyDateTime ?
  'USERNAME: ' $Username ?
;  'CDKey: ' $CDKey ?
	'CD Keys: ' ?
	For Each $Key in $aData
	$Key ?
	Next

Else

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

 $rs.fields.item("MonHersteller").value = $MonHersteller
 $rs.fields.item("MonitorDescription").value = $MonitorDescription
 $rs.fields.item("IP").value = $IP
 $rs.fields.item("Uhrzeit").value = $Uhrzeit
 $rs.fields.item("MAC").value = $MAC
 $rs.fields.item("PRIVILEGIEN").value = $Privilegien
 $rs.fields.item("COMPUTERNAME").value = $computername
 $rs.fields.item("OSNAME").value = $osname
 $rs.fields.item("OSTYPE").value = $OSType
 $rs.fields.item("SERVICEPACK").value = $ServicePack
 $rs.fields.item("INSTALLDATE").value = $InstallDate
 $rs.fields.item("LASTBOOTTIME").value = $LastBoottime
 $rs.fields.item("OSSERIAL").value = $OSSerial
 $rs.fields.item("SYSMANUFACTURER").value = $SysManufacturer
 $rs.fields.item("SYSMODEL").value = $SysModel
 $rs.fields.item("SYSSERIAL").value = $SysSerial
 $rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
 $rs.fields.item("CPUSPEED").value = $CPUSpeed
 ;$rs.fields.item("SYSMEMORY").value = $SysMemory
 $rs.fields.item("VIDEOCARD").value = $VideoCard
 $rs.fields.item("VIDEORES").value = $VideoRes
 ;$rs.fields.item("NICCARD").value = $NicCard
 $rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
 $rs.fields.item("USERNAME").value = $Username
 $rs.fields.item("CDKey").value = $CDKey
 $rs.update
 $rs.Close

EndIf

Exit 0



; ======================================================================
; functions follow...
; ======================================================================



; UDF WMIQuery
; ======================================================================
;FUNCTION	WMIQuery
;
;ACTION		Queries WMI information from supported systems
;
;AUTHOR		Radimus
;
;CONTRIBUTORS	kdyer, Shawn, And Howard
;
;		gbarnas: altered EXECUTE function for NoVarsInStrings support
;                        Added support for pre-auth object
;			 
;
;VERSION	2.4.2
;
;DATE CREATED	12/22/2001
;
;DATE MODIFIED	04/02/2007 - GAB - added pre-authenticated object pointer support
;
;KIXTART	4.x
;
;SYNTAX		WMIQuery(what,from,[computer],[where],[where_arg],[objAuth])
;
;PARAMETERS	what
;            
;
;		from
;            	 - Win32 Collection
;
;		optional computer
;		 - defaults to local PC
;
;		optional where
;		 - addl parameter for a 'WHERE' clause. Used with $x
;
;		optional where_arg
;		 - addl parameter for a 'WHERE' clause. Used with $Where
;
;		optional objAuth
;		 - pre-authenticated token obtained from WMIAuthenticate
;
;RETURNS	Array 
;		@error 1 = Cannot create COM object on target PC
;
;REMARKS	9/2003 - This release alters the return from the function into an ARRAY, where previous versions
;		used a pipe '|' delimited string.  If you are updating to this version, check your code closely!
;		2/2004 - Added support for authentication
;
;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 

Function WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x, Optional $root, Optional $pAuth)

  Dim $i, $sQuery, $objEnum, $sValue, $Tmp, $SystemSet, $objInstance 

  $sComputer = Trim(Join(Split($sComputer,'\'),''))

  If Not $sComputer Or $sComputer = @WKSTA
    $sComputer = '.'
  EndIf

  If Not $root
    $root = '\root\cimv2'
  Endif

  $sQuery = 'Select ' + $sWhat + ' From '+ $sFrom

  If $sWhere And $x
    $sQuery = $sQuery + " Where " + $sWhere + " = '" + $x + "'"
  EndIf

  If $pAuth
    $SystemSet = $pAuth
  Else
    $SystemSet = GetObject('winmgmts:{impersonationLevel=impersonate}!\\' + $sComputer + $root)
    If @ERROR Or Not $SystemSet
      Exit Val('&' + Right(DecToHex(@ERROR), 4))
    EndIf
  EndIf

  $objEnum = $SystemSet.ExecQuery($sQuery)
  If @ERROR Or Not $objEnum
    Exit Val("&" + Right(DecToHex(@ERROR), 4))
  EndIf

  For Each $objInstance in $objEnum
    $i = Execute(Chr(36) + 'sValue = ' + Chr(36) + 'objInstance.' + $sWhat)
    If VarType($sValue) & 8192
      $Tmp = $Tmp +'|' + Join($sValue,'|')
    Else
      $Tmp = $Tmp +'|' + $sValue 
    EndIf
  Next

  $WMIQuery = split(substr($Tmp,2),'|')
  Exit Val("&" + Right(DecToHex(@ERROR), 4))

EndFunction


; GET MS CD KEYS
; UDF from Allen. To be found under this link: http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=201729#Post201729
; ======================================================================

function Get_Product_Key($sproductid)
  Dim $aiKeyChars[24], $ilByte, $i, $iLOffset, $iUOffset, $bProductKey[15], $c, $nCur, $sCDKey
  For Each $c In Split("B C D F G H J K M P Q R T V W X Y 2 3 4 6 7 8 9")
    $aiKeyChars[$i]=Asc($c)
    $i=$i+1
  Next
  if len($sProductID)=2544
    $iLOffset=809
    $iUOffset=823
  else
    $iLOffset=53
    $iUOffset=67
  endif
  For $i = $iLOffset*2-1 To $iUOffset*2-1 Step 2
    $bProductKey[($i-($iLOffset*2-1))/2]=Execute("Exit &"+SubStr($sProductId,$i,2))
  Next
  For $ilByte = 24 To 0 Step -1
    $nCur = 0
    For $i=14 To 0 Step -1
      $nCur = $nCur * 256 ^ $bProductKey[$i] ; NOTE THE XOR! 
      $bProductKey[$i] = Int($nCur / 24)
      $nCur = $nCur Mod 24
    Next
    $sCDKey = Chr($aiKeyChars[$nCur]) + $sCDKey
    If $ilByte Mod 5 = 0 And $ilByte <> 0 $sCDKey = "-" + $sCDKey EndIf
  Next
  $Get_Product_Key=$sCDKey
EndFunction
 

Function SearchReg($Key,$Str,$SrcIn)
  Dim $Idx,$vName,$Value,$num,$SubKey,$fArr,$mbr
  $SearchReg = ''
  $num = 0
  $Idx = 0
  $vName = EnumValue($Key,$Idx)
  Do
    $mbr = ''
    If $SrcIn & 1
    $Value = ReadValue($Key,$vName)
     If InStr($Value,$Str)
       $mbr = $Key + "<=>" + IIf($vName,$vName,'<Default>')
     EndIf
    EndIf
    If ($SrcIn & 2) And InStr($vName,$Str)
      $mbr = $Key + "<=>" + $vName
    EndIf
    If $mbr
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $mbr
      $num = $num + 1
    EndIf
    $Idx = $Idx + 1
    $vName = EnumValue($Key,$Idx)
  Until @Error
  $Idx = 0
  $SubKey = EnumKey($Key,$Idx)
  While $SubKey
    If ($SrcIn & 4) And InStr($SubKey,$Str)
      ReDim Preserve $SearchReg[$num]
      $SearchReg[$num] = $Key + '\' + $SubKey + "<=><KeyName>"
      $num = $num + 1
    EndIf
    $fArr = SearchReg($Key + "\" + $SubKey,$Str,$SrcIn)
    If @Error = 0
      For Each $mbr In $fArr
        ReDim Preserve $SearchReg[$num]
        $SearchReg[$num] = $mbr
        $num = $num + 1
      Next
    EndIf
    $Idx = $Idx + 1
    $SubKey = EnumKey($Key,$Idx)
  Loop

  Exit VarType($SearchReg) = 8
EndFunction
 

Function ArrayAdd($Array1, $Array2)
 ;Returns a new $Array1 
 Dim $n,$i
 $n = UBound($Array1) + 1
 REDIM PRESERVE $Array1[$n+UBound($Array2)]
 For $i = 0 to UBound($Array2)
   $Array1[$n+$i] = $Array2[$i]
 Next
 $ArrayAdd = $Array1
EndFunction


; UDF MEMORY
; ======================================================================
;FUNCTION		Memory() 
; 
;AUTHOR		Glenn Barnas
; 
;ACTION		Returns the amount of Available Physical RAM in a local or remote system 
; 
;SYNTAX		Memory([system]) 
; 
;VERSION		4.0 
; 
;DATE			v1.0 - 2004/02/04
; 
;DATE REVISED		v2.0 - 2005/02/25 - updated to allow larger memory sizes (x86)
;			v3.0 - 2007/10/05 - rewrite to support x64 systems, tighten code
;			v4.0 - 2013/11/02 - rewrite to detect large memory blocks on post-Vista platforms
; 
;PARAMETERS		System - OPTIONAL - name of system to query. Default is local system
;
;REMARKS		Returns Physical RAM size available to the O/S using registry memory allocation map
;			Returns @ERROR on registry read failure, or 13 / "Data is Invalid" if reg is empty
;			DOES NOT return physical hardware ram value! Some systems allocate RAM to BIOS cache 
;			or video adapters and this memory is not reported. This can be adjusted for using
;			the second example below.
; 
;RETURNS		Integer - Available Physical RAM (in Megabytes) 
; 
;DEPENDENCIES		None 
; 
;TESTED WITH		WinXP, Vista, Win7, Win8
;			Windows Server 2000, 2003, 2008, 2012
;			Tested with up to 16G of RAM 
; 
;EXAMPLES		$RAM = Memory('ThatPC')		; Get the available RAM from a remote computer
;			
;			; Adjust for BIOS/Video ram to estimate total physical RAM by using the smallest
;			; deployed DIMM module as an increment. Use "1024" for full GB increments.
;			; If the GB flag is used, this method must be adjusted to define MSize in GB values
;			; (e.g. 0.5 instead of 512). 
;			$MSize = 512			; Size of smallest RAM module (DIMM)
;			
;			$Mem = Memory()			; get reported memory
;			$Ma  = $Mem / $MSize		; determine number of modules installed
;			
;			; If a fractional module is detected, add another full module
;			; this accounts for a fractional module used for BIOS cache or video RAM
;			If $Mem Mod $MSize $Ma = $Ma + 1 EndIf
;			
;			; $Mem now holds a value based more closely on installer rather than available RAM
;			$Mem = $Ma * $MSize		; Memory is #_modules * Mod_Size
;			
; 

Function Memory(Optional $_System)

  DIM $_Block				;
  Dim $_MemMap				; Physical Memory Map
  Dim $_Start, $_End, $_Step		; for/next start & step increment
  Dim $_Sum				; running sum of memory from map
  Dim $_Idx				; temporary index var
  Dim $_iMem				; Memory region size index
  Dim $_Error				; Error placeholder

  ; Insure $_System has "\\System\" format if it is specified 
  If $_System <> ''
    $_System = '\\' + Join(Split($_System, '\'), '', 3) + '\'
  EndIf
  ; Get the memory value from the registry 
  $_Idx = $_System + 'HKLM\hardware\resourcemap\system resources\physical memory'
  $_MemMap = ReadValue($_Idx, '.Translated')
  ; Check for invalid read and Return 
  If Len($_MemMap) = 0 Or @ERROR
    $Memory = 0				; return 0 Mbytes 
    $_Error = IIf(@ERROR, @ERROR, 13) 	; Return "Data is Invalid" if no error but data is blank 
    Exit $_Error
  EndIf
  ; determine system's O/S type based on architecture
  $_Idx = $_System + 'HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
  If ReadValue($_Idx, 'PROCESSOR_ARCHITECTURE') = 'AMD64'
    $_Step  = 40	; Define the step size based on the O/S Architecture
    $_Start = 33	; Offset where memory descriptor blocks start
    $_Sum   = 0.0	; no unreported base memory to account for
    $_iMem  = 10	; Memory size index
  Else
    $_Step  = 32	; Define the step size based on the O/S Architecture
    $_Start = 41	; Offset where memory descriptor blocks start
    $_Sum   = 737280.0	; account for base memory not identified in memory map - x86 only
    $_iMem  = 2		; Memory size index
  EndIf
  $_End = Len($_MemMap) - 8
  For $_Idx = $_Start to $_End Step $_Step
    $_Block = SubStr($_MemMap, $_Idx, $_Step)
    If SubStr($_Block, $_iMem, 1) > 3
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 4294967296.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 9, 2)))
    Else
      $_Sum = $_Sum + CDbl(Val('&' + SubStr($_Block, $_Step - 1, 2))) * 16777216.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 3, 2))) * 65536.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 5, 2))) * 256.0
                    + CDbl(Val('&' + SubStr($_Block, $_Step - 7, 2)))
    EndIf
  Next
  ; Sum is in Bytes - return the total as megabytes
  $Memory = CInt($_Sum / 1048576)
  Exit 0

EndFunction


backfight

Top
Page 4 of 5 <12345>


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

Who's Online
0 registered and 315 anonymous users online.
Newest Members
Praveer, Ollero, rayyo, Foxlicht, DonChild
17742 Registered Users

Generated in 0.048 seconds in which 0.015 seconds were spent on a total of 14 queries. Zlib compression enabled.

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