'Author:				Tom Waller
'Date Created:			30/04/2007
'Date Modified:			30/04/2007
'Revision:				1.0

'Logon script for recording computer inventory information. Once gathered, logs data
'to SQL database for analysis. Created by Tom Waller.

'************************************************
'* PHASE 1 - Gather information                 *
'************************************************

'Set the script to continue on error. Handy in case the SQL server is down.
On Error Resume Next
'Establish a network object for interaction.
Set oNetwork = CreateObject("WScript.NetWork")
'Do the same for the shell, this is used mostly for REGWRITE.
Set oShell = CreateObject("WScript.Shell")

'Get basic network information.
sUserName = oNetwork.UserName
sHostName = oNetwork.ComputerName
sDomainName = oNetwork.UserDomain
sDate = Date
sTime = Time
sLastLogon = sDate & " " & sTime

'Establish a Wmi object for interaction. This has to be applied after
'the variable sHostName has been assigned.
oMgmt = "winmgmts:{impersonationLevel=impersonate}!//"& sHostName &""
'Establish the Wmi service for recording the IP address.
Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sHostName & "\root\cimv2")

'This for loop goes through each of the serial numbers stored in the
'BIOS and pulls the Service Tag record from it. The Service Tag
'is usually stored in the 1st serial number container.
Set sSerials = oWMIService.ExecQuery("Select * from Win32_BIOS") 
for each sSerial in sSerials
	sServiceTag = sSerial.SerialNumber
Next

'Select the IP enabled network interfaces in the users computer.
Set IPConfigSet = oWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
'Pull the address.
For Each IPConfig in IPConfigSet
    If Not IsNull(IPConfig.IPAddress) Then 
        For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
            sAddress = IPConfig.IPAddress(0)
        Next
    End If
Next

'************************************************
'* PHASE 2 - Read / Write Data to Registry      *
'************************************************

'Get the operating system version from the remote registry.
sKeyOS = "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProductName"
sOperatingSystem = oShell.RegRead(sKeyOS)

'Read the template version from the registry.
sKeyTemplates = "HKLM\Software\DTZ\Computer\TemplateVer"
sTemplateVer = oShell.RegRead(sKeyTemplates)

'Store the registry paths for the RegWrite coming up.
sKeyTag = "HKLM\Software\DTZ\Computer\ServiceTag"
sKeyLastUser = "HKLM\Software\DTZ\Computer\LastUser"
sKeyHostName = "HKLM\Software\DTZ\Computer\HostName"
sKeyAddress = "HKLM\Software\DTZ\Computer\IP"
sKeyLastLogon = "HKLM\Software\DTZ\Computer\LastLogon"

'Write the discovered values to the computer registry. This may be of use later.
oShell.RegWrite sKeyTag, sServiceTag, "REG_SZ"
oShell.RegWrite sKeyLastUser, sUserName, "REG_SZ"
oShell.RegWrite sKeyHostName, sHostName, "REG_SZ"
oShell.RegWrite sKeyAddress, sAddress, "REG_SZ"
oShell.RegWrite sKeyLastLogon, sLastLogon, "REG_SZ"

'************************************************
'* PHASE 3 - Write Data to SQL Database         *
'************************************************

'Create the object to store the database connection.
set oConn = CreateObject("ADODB.Connection")
'And do the same with the recordset object.
set oRecordSet = CreateObject("ADODB.Recordset")
'Store the SELECT query.
sSelectQuery = "SELECT * FROM Computers WHERE cHostName = '" & sHostName & "'"
'The INSERT SQL query used to add the data to the database.
sInsertQuery = "INSERT INTO Computers ( [cHostName], [cLastUser], [cServiceTag], [cOS], [cTemplateVer], [cLastLogon], [cDateAdded], [cAddress] ) VALUES ( '" & sHostName & "', '" & sUserName & "', '" & sServiceTag & "', '" & sOperatingSystem & "', '" & sTemplateVer & "', '" & sLastLogon & "', '" & sLastLogon & "', '" & sAddress & "' )"
'DELETE query. Used for testing only. Do not invoke unless you know what your doing.
sDeleteQuery = "DELETE FROM Computers WHERE cID = '111'"
'Connection string to the SQL database (lsdata on DEPLOY001).
sConnString="Provider=SQLOLEDB.1;Data Source=DEPLOY001; Initial Catalog=lsdata;Integrated Security=SSPI;"

'Establish connection to the database.
oConn.open = sConnString
'Set the recordset.
set oRecordSet = oConn.execute(sSelectQuery)

'Check to see if the record exists.
if (oRecordSet.BOF and oRecordSet.EOF) then
	'No record found for this computer. Execute the INSERT statement.
	oConn.execute(sInsertQuery)
else
	oRecordSet.movefirst
	do until oRecordSet.EOF
		'The UPDATE SQL query used to add the data to the database. The
		'UPDATE query has to be declared after the tID variable has
		'been initialised as it uses tID for an anchor to a record.
		sUpdateQuery = "UPDATE Computers SET " & _
			"cLastUser = '" & sUserName & "', " & _
			"cServiceTag = '" & sServiceTag & "', " & _
			"cOS = '" & sOperatingSystem & "', " & _
			"cTemplateVer = '" & sTemplateVer & "', " & _
			"cLastLogon = '" & sLastLogon & "', " & _
			"cAddress = '" & sAddress & "' " & _
			"WHERE cHostName = '" & sHostName & "'"

		'Run the update.
		oConn.execute(sUpdateQuery)
		'Move to the next record, in case more than one was selected.
		oRecordSet.movenext
	loop
end if

'************************************************
'* PHASE 4 - House Keeping                      *
'************************************************

set oNetwork = Nothing
set oShell = Nothing
set oMgmt = Nothing
set oWMIService = Nothing
set IPConfigSet = Nothing
set sSerials = Nothing
set oConn = Nothing
set oRecordSet = Nothing

'************************************************
'* Database Schema                              *
'************************************************

'cID | CHostName | cServiceTag | cLastUser | cOS | cOSVer | cTemplateVer | cLastLogin | cDateAdded | cAddress