Updated Script: Writes to SQL DB, GET ALL SOFTWARE BUT IN TWO DB.FIELDS

;*** CREATE A SQL DATABASE WITH A TABLE NAMED COMPUTERS****
;*** CREATE THE FOLLOWING FIELDS: COMPUTERNAME, DOMAIN, SYSNAME
;*** OSNAME, MANUFACTURER, OSVERSION, OSBUILD, OSTYPE, LANGUAGE
;*** SERVICEPACK, BOOTDEVICE, SYSTEMDEVICE, WINDIR, SYSDIR, INSTALLDATE
;*** LASTBOOTTIME, OSSERIAL, SYSMANUFACTURER, SYSMODEL, SYSSERIAL, SYSBIOSVER
;*** SYSBIOSDATE, CPUMANUFACTURER, CPUDESCRIPTION, CPUSPEED, MAXCLOCK, FAMILY
;*** STEPPING, DEVICEID, SYSMEMORY, DISKNAMES, VOLNAMES, DISKNAMES, DISKSIZE
;*** FILESYS, FREESPACE, VIDEOCARD, VIDEORES, PRINTERS, NICCARD, MODIFYDATETIME
;*** APPLICATIONS, APPLICATIONS2, SERVICES, USERNAME,

CLS
AT (1,1) "Your IT Department is conducting an Asset Audit of this Workstation."
AT (2,1) "Please wait while the information is collected."

;**** DECLARE VARIABLES ***

DIM $CNstring, $CMDtxt, $cn, $cmd, $rs
DIM $ModifyDateTime, $computername, $username, $domain, $sysname, $osname
DIM $Manufacturer, $OSVersion, $OSBuild, $OSType, $Language, $ServicePack
DIM $BootDevice, $SystemDevice, $WinDir, $SysDir, $InstallDate, $LastBoottime
DIM $OSSerial, $SysManufacturer, $SysModel, $SysSerial, $SystemBIOSVer, $SysBIOSDate
DIM $CPUManufacturer, $CPUDescription, $CPUSpeed, $MaxClock, $Family, $Stepping
DIM $DeviceID, $SysMemory, $DiskNames, $VolNames, $DiskSize
DIM $FileSys, $FreeSpace, $VideoCard, $VideoRes, $Modem, $Printers, $ptr, $NicCard, $nic
DIM $Applications, $Services, $Applications2, $InstalledSoftwareArray
DIM $element, $Software



function ASort($array, OPTIONAL $order)
; sort order: 0 = ascending, 1 = decending
dim $index, $x, $y, $tmp, $changed
$Asort=$array
$order=0+$order
do
$changed=0
for $index = 0 to ubound($asort)-1
$x=$asort[$index]
$y=$asort[$index+1]
if ($x > $y and 1-$order) or ($x < $y and $order)
$tmp=$x
$asort[$index]=$y
$asort[$index+1]=$tmp
$changed=1
endif
next
until $changed=0
endfunction

FUNCTION GetSoftwareList (OPTIONAL $sort)
DIM $arrayindex, $enumindex, $component, $dn, $regkey, $swarray[200]
$regkey='HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall'
$enumindex=0
$arrayindex=0
:gisloop
$component=EnumKey($regkey, $enumindex)
IF NOT @error
IF NOT (Len($component)=7 AND Left($component,1)='Q')
; above line excludes hotfixes from listing
IF NOT (0+ReadValue($regkey+'\'+$component,'SystemComponent'))
$dn=ReadValue($regkey+'\'+$component,'DisplayName')
IF $dn
$swarray[$arrayindex]=$dn
$arrayindex=$arrayindex+1
ENDIF
ENDIF
$enumindex=$enumindex+1
GOTO gisloop
ENDIF
ENDIF
REDIM PRESERVE $swarray[$arrayindex]
IF $sort
$getsoftwarelist=asort($swarray)
ELSE
$getsoftwarelist=$swarray
ENDIF
ENDFUNCTION ; - GetSoftwareList -


;*** CONFIGURE DATABASE CONNECTION STRING ***
$CNstring = "DRIVER={SQL Server};SERVER=MTCDEV1;UID=assetsdb;PWD=assetsdb;DATABASE=Assets"
$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)

;*** COLLECT WORKSTAION ASSET INFORMATION

$ModifyDateTime = @DATE + " " + @TIME
$computername = @WKSTA
$username = @userid
$domain = @domain
$sysname = WMIQuery("CSName","Win32_OperatingSystem")
$osname = WMIQuery("Caption","Win32_OperatingSystem")
$Manufacturer = WMIQuery("Manufacturer","Win32_OperatingSystem")
$OSVersion = WMIQuery("Version","Win32_OperatingSystem")
$OSBuild = WMIQuery("BuildNumber","Win32_OperatingSystem")
$OSType = WMIQuery("BuildType","Win32_OperatingSystem")
$Language = WMIQuery("OSLanguage","Win32_OperatingSystem")
$ServicePack = WMIQuery("CSDVersion","Win32_OperatingSystem")
$BootDevice = WMIQuery("BootDevice","Win32_OperatingSystem")
$SystemDevice = WMIQuery("SystemDevice","Win32_OperatingSystem")
$WinDir = WMIQuery("WindowsDirectory","Win32_OperatingSystem")
$SysDir = WMIQuery("SystemDirectory","Win32_OperatingSystem")
$InstallDate = WMIQuery("InstallDate","Win32_OperatingSystem")
$LastBoottime = WMIQuery("LastBootupTime","Win32_OperatingSystem")
$OSSerial = WMIQuery("SerialNumber","Win32_OperatingSystem")
$SysManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SysModel = WMIQuery("Model","Win32_ComputerSystem")
$SysSerial = WMIQuery("SerialNumber","Win32_BIOS")
$SystemBIOSVer = WMIQuery("SMBIOSBIOSVersion","Win32_BIOS")
$SysBIOSDate = WMIQuery("Version","Win32_BIOS")
$CPUManufacturer = WMIQuery("manufacturer","Win32_Processor")
$CPUDescription = WMIQuery("description","Win32_Processor")
$CPUSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor")
$MaxClock = WMIQuery("maxclockspeed","Win32_Processor")
$Family = WMIQuery("family","Win32_Processor")
$Stepping = WMIQuery("stepping","Win32_Processor")
$DeviceID = WMIQuery("deviceid","Win32_Processor")
$SysMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
$DiskNames = WMIQuery("name","Win32_LogicalDisk")
$VolNames = WMIQuery("volumename","Win32_LogicalDisk")
$DiskSize = WMIQuery("size","Win32_LogicalDisk")
$FileSys = WMIQuery("filesystem","Win32_LogicalDisk")
$FreeSpace = WMIQuery("freespace","Win32_LogicalDisk")
$VideoCard = WMIQuery("Description","Win32_VideoController")
$VideoRes = WMIQuery("VideoModeDescription","Win32_VideoController")
$Modem = WMIQuery("Description","Win32_POTSModem")
$Printers = WMIQuery("Name","Win32_printer")
$Applications = WMIQuery("Caption","Win32_Product")
$InstalledSoftwareArray=GetSoftwareList(1)
$Software = Join($InstalledSoftwareArray,"|")
$Services = WMIQuery("Caption","Win32_Service")
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

endif
next

FUNCTION WMIQuery($what,$where,)
dim $strQuery, $objEnumerator, $value
$strQuery = "Select $what From $where"
$SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//@WKSTA")
$objEnumerator = $SystemSet.ExecQuery($strQuery)
For Each $objInstance in $objEnumerator
If @Error = 0 and $objInstance <> ""
$=execute("$$value = $$objInstance.$what")
$WMIQuery="$value"+"|"+"$WMIQuery"
EndIf
Next
$WMIQuery=left($WMIQuery,len($WMIQuery)-1)
exit @error
ENDFUNCTION

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

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

$rs.fields.item("COMPUTERNAME").value = $computername
$rs.fields.item("DOMAIN").value = $domain
$rs.fields.item("SYSNAME").value = $Sysname
$rs.fields.item("OSNAME").value = $osname
$rs.fields.item("MANUFACTURER").value = $Manufacturer
$rs.fields.item("OSVERSION").value = $OSVersion
$rs.fields.item("OSBUILD").value = $OSBuild
$rs.fields.item("OSTYPE").value = $OSType
$rs.fields.item("LANGUAGE").value = $Language
$rs.fields.item("SERVICEPACK").value = $ServicePack
$rs.fields.item("BOOTDEVICE").value = $BootDevice
$rs.fields.item("SYSTEMDEVICE").value = $SystemDevice
$rs.fields.item("WINDIR").value = $WinDir
$rs.fields.item("SYSDIR").value = $SysDir
$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("SYSBIOSVER").value = $SystemBIOSVer
$rs.fields.item("SYSBIOSDATE").value = $SysBIOSDate
$rs.fields.item("CPUMANUFACTURER").value = $CPUManufacturer
$rs.fields.item("CPUDESCRIPTION").value = $CPUDescription
$rs.fields.item("CPUSPEED").value = $CPUSpeed
$rs.fields.item("MAXCLOCK").value = $MaxClock
$rs.fields.item("FAMILY").value = $Family
$rs.fields.item("STEPPING").value = $Stepping
$rs.fields.item("DEVICEID").value = $DeviceID
$rs.fields.item("SYSMEMORY").value = $SysMemory
$rs.fields.item("DISKNAMES").value = $DiskNames
$rs.fields.item("VOLNAMES").value = $VolNames
$rs.fields.item("DISKNAMES").value = $DiskNames
$rs.fields.item("DISKSIZE").value = $DiskSize
$rs.fields.item("FILESYS").value = $FileSys
$rs.fields.item("FREESPACE").value = $FreeSpace
$rs.fields.item("VIDEOCARD").value = $VideoCard
$rs.fields.item("VIDEORES").value = $VideoRes
$rs.fields.item("PRINTERS").value = $Printers
$rs.fields.item("NICCARD").value = $NicCard
$rs.fields.item("MODIFYDATETIME").value = $ModifyDateTime
$rs.fields.item("APPLICATIONS").value = $Applications
$rs.fields.item("APPLICATIONS2").value = $Software
$rs.fields.item("SERVICES").value = $Services
$rs.fields.item("USERNAME").value = $Username

$rs.update

$rs.Close