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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

;Collect share info on the local machine
$WMI_SHARE = "SELECT * FROM WIN32_SHARE"
$Shares = GetObject($WMI).ExecQuery($WMI_SHARE)
;? "Get share info from WMI " +  @ERROR + ": " + @SERROR Get $x
For Each $Share In $Shares
	$Name = $Share.Name
	$Path = $Share.Path
	$Desc = $Share.Description
	$Type = $Share.Type
	Select
		Case $Type = 0
			$Type = "Disk Drive"
		Case $Type = 1
			$Type = "Print Queue"
		Case $Type = 2
			$Type = "Device"
		Case $Type = 3
			$Type = "IPC"
		Case $Type = 2147483648
			$Type = "Disk Drive Admin"
		Case $Type = 2147483649
			$Type = "Print Queue Admin"
		Case $Type = 2147483650
			$Type = "Device Admin"
		Case $Type = 2147483651
			$Type = "IPC Admin"
	Endselect
	;Type uint32 Read-only 
	;Type of resource being shared. Types include disk drives, print queues, interprocess communications (IPC), and general devices.
	;Values are:
	;0 = Disk Drive
	;1 = Print Queue
	;2 = Device
	;3 = IPC
	;2147483648 = Disk Drive Admin
	;2147483649 = Print Queue Admin
	;2147483650 = Device Admin
	;2147483651 = IPC Admin
	$Hyperlink = "\\" + @WKSTA + "\" + $Name
	$SQL_SHARES = "SELECT * FROM TBL_SHARES WHERE COMPID=" + $PCIDENTIFIER + " AND SHARENAME='" + $NAME + "'"
	$Command.CommandText = $SQL_SHARES
	$Recordset.Open($Command)
	;? "Open Share recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
		;? "Add new share record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("ShareName").Value = $Name
	$Recordset.Fields("SharePath").Value = $Path
	$Recordset.Fields("Description").Value = $Desc
	$Recordset.Fields("ShareType").Value = $Type
	$Recordset.Fields("Hyperlink").Value = $Hyperlink
	$Recordset.Update
	;? "Update share record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close share recordset " + @ERROR + ": " + @SERROR Get $x
Next
 
;And now grab Network Adapter info and update specific table
$WMI_NETWORK_1 = "SELECT * FROM WIN32_NETWORKADAPTERCONFIGURATION WHERE IPENABLED='TRUE'"
$NetCards = GetObject($WMI).ExecQuery($WMI_NETWORK_1)
;? "Get simple adapter info from WMI " + @ERROR + ": " + @SERROR Get $x
For Each $Card In $NetCards
	$DeviceID = $Card.Index
	;? "Device ID is " + $DeviceID
	$MACAddress = $Card.MACAddress
	;? "MAC Address is " + $MACAddress
	$NetCard = $Card.Description
	;? "Net card description is " + $NetCard
	$IPAddress = $Card.IPAddress
	;? "IP Address is " + $IPAddress[0]
	$DHCP = $Card.DHCPEnabled
	If $DHCP
		$DHCP = "DHCP Enabled"
	Else
		$DHCP = "Static address"
	Endif
	;? "DHCP state is " + $DHCP
	$SQL_NETWORK = "SELECT * FROM TBL_NETWORKADAPTERS WHERE COMPID=" + $PCIDENTIFIER + " AND DEVICEID=" + $DeviceID + ";"
	$Command.CommandText = $SQL_NETWORK
	$Recordset.Open($Command)
	;? "Open Adapters recordset " + @ERROR + ": " + @SERROR Get $x
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
		;? "Add new adapters record " + @ERROR + ": " + @SERROR Get $x
	Endif
	$Recordset.Fields("compID").Value = $PCIdentifier
	$Recordset.Fields("DeviceID").Value = $DeviceID
	$Recordset.Fields("NetCard").Value = $NetCard
	$Recordset.Fields("IPAddress").Value = $IPAddress[0]
	$Recordset.Fields("MACAddress").Value = $MACAddress
	$Recordset.Fields("DHCP").Value = $DHCP
	$Recordset.Update
	;? "Update net card record " + @ERROR + ": " + @SERROR Get $x
	$Recordset.Close()
	;? "Close net card recordset " + @ERROR + ": " + @SERROR Get $x
Next

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

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

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

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

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

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