Tuesday, 25 March 2008

How to keep computer history after a motherboard was replaced

Article ID: 24859
Deployment Solution 6.1 all buildsDeployment Solution 6.5 all builds
By changing motherboards the MAC address gets changed, and this will create a new entry in the eXpress database with new history. The old history will not be linked to thecomputer.
Right-click on the new computer. Go to Properties > General tab, and find the ID of the computer per example: ID 50002345; write it down. Find the old computer per example: ID 50002332 and write down the ID.
Delete the new computer from the DS console and don’t let it reconnect.
Open SQL Enterprise Manager and go to the eXpress database.
Once in the eXpress database, highlight Tables and find the NICS table in the right-hand side.
Right-click on the NICS table and open table > return all rows.
Find the ID of the old computer (ID 50002332) and then replace the old MAC address of this computer with the new MAC address of the new computer (ID 50002345) in the mac_addr column then delete the new computer ID from the NICS table.
Save the changes and close Enterprise Manager.
Turn the new computer on and double check that the history of the old MAC address is reflected on the computer with the new MAC.

VBscript to remove all traces of computers from DS Database

Article ID: 26082
Running the following VBScript will remove all traces of computers from the DS database:
Option Explicit ‘**** The script displays a list of computer_ids and the associated computer name and ‘ allows the user to enter the computer_id that it to be removed from the express ‘ database. All occurrences of the record are removed from the database ‘ADO Const conADOStateClosed = &H00000000 Const conADOStateOpen = &H00000001 Const adStateClosed = 0 Dim strErr dim cmdArgs ‘**************** Call the Delete Computer function ************************* DeleteComputer ‘Line 18 ‘******************************************************************************** ‘Function to capture error messages ‘******************************************************************************** Function GetErrorString(objErr, strContext) Dim strError strError = "An error has occurred. " If (strContext <> "") Then strError = strError & strContext & ". " If (objErr.description <> "") Then strError = strError & objErr.description Else strError = strError & "Error number " & objErr.number End If GetErrorString = strError objErr.Clear End Function ‘Line 40 ‘*********************************************************************************** ‘Function to insert parameter from command line (computer_id) into the query ‘ ‘********************************************************************************* Sub InsertIdValue(objConn, lComputer_Id) On Error Resume Next ‘wscript.echo "The computer_id value is " & lComputer_id Dim strSQL, strErr ‘wscript.echo "Ready to insert" & lComputer_Id & " into database" strSQL = "INSERT INTO ##DeleteComputer Values(" + CStr(lComputer_Id) + ")" objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete computer " & strComputer_Id) logMessage strErr, 1 End If End Sub ‘Line 62 ‘*************************************************************************************** ‘Function to delete the computer selected ‘ The first part of this is providing the data to make connection to the SQL database ‘******************************************************************************* Function DeleteComputer On Error Resume Next ‘Line 73 Dim objConn, objAdmin Dim strTableName, strSQL, strDBName, strDBMachine, strDBUserPW, strDBUser, strConnection Dim objRecSet, strTabs, strDelete, strTableNumber, strHostType, strDummy, strComputer_Id Dim objRs, objTest, strAllComputers, intCountObjects, intStringLength, objDiskRs, strComputerNumber Dim gstrServer, gstrDatabase, gstrUser, gstrPwd, objWShell, strRegLocation, strUseCmdLine, strIBTxt, strInstruct strHostType = right(wscript.fullname,11) strHostType = lcase(left(strHostType,7)) ‘Line 84 ‘**************Connection to the database**************** Set objConn = CreateObject("ADODB.Connection") objConn.Open strConnection if err.number <> 0 then LogError "Error - doing SQL connection as the current logged on user.", err.number, err.description strDBUser = inputbox("Please enter a SQL user -" & vbCRLF & "(sa will always work if you have the password)" & vbCRLF & "The password will be asked for in the next box.", "SQL logon", "Enter SQL user") strDBUserPw = inputbox("Please enter the SQL user password", "SQL password", "Enter SQL user password") Err.clear strConnection = strConnection & ";UID=" & strDBUser & ";PWD=" & strDBUserPw objConn.Open strConnection If err.number <> 0 then LogError "Error - doing SQL connection with new user and pw.", err.number, err.description strDBMachine = inputbox("Please enter the SQL server machine name", "SQL machine name", "Enter SQL machine name") strDBName = "express" Err.clear strConnection = "DRIVER={SQL Server};SERVER=" & strDBMachine & ";DATABASE=" &strDBName & ";UID=" & strDBUser & ";PWD=" & strDBUserPw ‘Line 103 objConn.Open strConnection If err.number <> 0 then LogError "Error - doing SQL connection with new machine name, db, user and pw.", err.number, err.description LogError left(strConnection, inStrRev(strconnection,"PW") - 1), err.number, err.description Wscript.echo "The connection to SQL was not created." & vbCRLF & vbCRLF & "The script will halt now. Please resolve the SQL connection error." ‘wscript.echo "DBName is " & strDBName wscript.quit ‘Line 111 Else LogError "SQL connection with SQL user name, password, machine and db name.", err.number, err.description End if else LogError "SQL connection with SQL user name and password.", err.number, err.description End If Else LogError "SQL connection as the current logged on user.", err.number, err.description End If objConn.CommandTimeout = 600 ‘Line 119 ‘******************Create a global temp table of ************** strSQL = "if isnull(object_id(’tempdb..##DeleteComputer’), 0)<>0 Delete From ##DeleteComputer " +_ "else CREATE TABLE ##DeleteComputer ([Computer_Id] [int] PRIMARY KEY NOT NULL)" objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete computer_Id " & strComputernumber) logMessage strErr, 1 End If On Error Goto 0 on Error Resume Next ‘Line 136 ‘********************************************************************************************************************* ‘ Display list of computers in Database ‘************************************************************************************* strSQL = "Select computer_id, [Name] from Computer" Set objRs = objConn.Execute(strSql) intCountObjects = 0 strInstruct = "If you do not want to delete any computers, enter 0 in the input box." + vbCrLF + vbCrLF + strInstruct strInstruct = "When the input box appears, enter the number." + vbCrLF + strInstruct strInstruct = "Record the Computer ID of the computer you wish to delete." + vbCrLF + strInstruct strInstruct = "Press to see the next set of 100 computers." + vbCrLF + strInstruct strInstruct = "A list containing the first 100 computers will appear." + vbCrLF + strInstruct wscript.echo strInstruct While Not objRs.EOF ‘Line 154 ‘********************** Display the computers in groups of 100 ******************* While (intCountObjects < strallcomputers =" strAllComputers" intcountobjects =" intCountObjects" intcountobjects =" 0" strallcomputers = " " strcomputernumber =" InputBox" objconn =" Nothing"> 0 Then ‘wscript.echo "Err = " + Err.Description ‘End If ‘************* Call the InsertIdValue function to insert value in the temp table *************** InsertIdValue objConn, strComputerNumber ‘****************** ‘Get table list with computer_id column******************* ‘Line 182 strSQL = "select DISTINCT name as TableName from sysobjects where xtype=’U’ and id in (select distinct id from syscolumns where name = ‘Computer_Id’)" Set objRecSet = objConn.Execute(strSQL) If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete Computer " & strComputernumber) logMessage strErr, 1 End If strSQL = "" If (objRecSet.State <> adStateClosed) And (Not objRecSet.EOF) Then While (Not objRecSet.EOF) strTableName = objRecSet.Fields.Item(0).value ‘Join temp table with table to delete joint rows strSQL = "delete [" + strTableName + "] from ##DeleteComputer, [" + strTableName + "] where ##DeleteComputer.Computer_Id = [" + strTableName + "].Computer_Id" ‘Line 199 objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete Computer " & strComputernumber) logMessage strErr, 1 End If objRecSet.MoveNext() Wend wscript.echo "Finished Deleting Computer " & strComputernumber wscript.quit End If objRecSet.Close Set objRecSet = Nothing ‘Line 217 End Function ‘Completed - 11/12/2004 by Ken Spinder
You can also download the attached file; just remember to remove the ‘.txt’ at the end of the filename.

How to clean out old computers in Deployment Server database

Article ID: 24561
Here is a Script to delete all old computers manually. Within the Deployment Server 6.5 build > Tools > Options, check the box to clean out old computers by date range.There is a SQL script you can run in Query Analyzer that will clean out computers that have not reported inventory in 35 days. To use this script just open SQL Query Analyzer from the SQL Administrations tools. Select the eXpress Database then copy the following code into the Query Analyzer window and click run.
DELETE from computerWHERE datediff(dd,last_inventory,getdate())>35
Here is a sample Deployment Server job that can be scheduled in DS to run regularly (daily/monthly) that will automate this task. It is a server-side script and must be assigned to a client PC before it can run on the Deployment server. You can schedule it on any computer that will likely be on most of the time; using scheduling have it run once a month.Without modification it assumes SQL is on the same server as DS and that you are using SA with a blank password. It will delete any computer older than 30 days. It is self-explanatory how to change this behavior. Just look at the script (%DAYS%, %SQLSERVER%, %SQLUSER%, and %SQLPASS%).Steps to create this job:
Create a Run script event that runs in Windows. Here is the Script to use for this event:REM This will delete entries that are older than DAYS from the eXpress databaseSET DAYS=30SET SQLSERVER=localhostREM Use the following line for trusted authentication. Note that the script will run by default as LOCAL SYSTEM and may not have rights to the SQL server.REM osql -E -S %SQLSERVER% -d eXpress -Q "DELETE from computer WHERE datediff(dd,last_inventory,getdate())>%DAYS%" >> %WINDIR%\Temp\DS_Delete.log
REM Use the following lines for SQL authentication. You will need to supply an appropriate SQL username and password.SET SQLUSER=saSET SQLPASS=""osql -U %SQLUSER% -P %SQLPASS% -S %SQLSERVER% -d eXpress -Q "DELETE from computer WHERE datediff(dd,last_inventory,getdate())>%DAYS% " >> %WINDIR%\Temp\DS_Delete.log
NOTE: If Inventory is turned off then Last_Inventory will not be an effective or reliable way to target which computers need to be deleted. If Inventory collection is not enabled then the following query will be better to use in the previous scripts and or command lines:
DELETE FROM computer WHERE computer_id IN (select computer_id from sessions where datediff(dd, last_update, getdate()) > 35)

Thursday, 13 March 2008

Excluding/Controlling what Client MAC is reported to the DS Server

Many computers have multiple NICs, which can cause problems in Deployment Solution. For instance, a computer may only have one NIC connected, and Deployment Solution may report on the second NIC with no IP address, thus showing a missing IP address in the console. Clustered computers may have a single assigned MAC given to multiple computers, again causing problems within Deployment Solution if the incorrect MAC is tracked.Computers reporting multiple NICs may also encounter Deployment Server licensing issues when each NIC is assigned a separate license.
DS 5.x and 6.x all versions
Because DS tracks a computer based only on a single NIC/MAC address, sometimes human intervention is necessary to prevent conflicts or incorrect data.
A CustomData.ini file can be created to exclude NICs or MACs so that DS will only track the remaining NIC/MAC option. This file is created by duplicating the Dynamic.ini file, and renaming the duplicate to CustomData.ini. This can either be performed on the client computer directly, or on the server and then sent out with a job to the client computers.First, be sure there is a [Filters] section in this file. Since DS combines the contents of both the Dynamic.ini and CustomData.ini files, the CustomData.ini file does not need to have all the same information as the Dynamic.ini. However, for this exercise, at least the [Filters] section is required.In this section, a line may be modified (or created) with "FilteredAdapterNameList" to include custom/unique types of adapters which should be excluded or ignored by DS (such as dial-up adapters, or WiFi adapters). The line should look something like this: FilteredAdapterNameList=Dial-Up Adapter,VPN,PPP AdapterAlternatively, a "FilteredMACAddresses" line may be used to exclude certain the MAC addresses on adapters that may be otherwise legitimate. This would be the case for duplicate NICs, such as having two identical Intel NICs, where the above option would end up excluding both. This line is used to exclude specific MAC addresses, rather than a type of connection. This line should look something like this: FilteredMACAddresses=CCCCCCCCCCCC,FFFFFF000000,00038A000011Finally, for those who may need it, there is one other way to filter, and that is based on user. By default, we already exclude one user, but you may add others. The FilteredUserNames line looks like this (additional entries, as with the above options, should be seperated by commas): FilteredUsernames=SMSCliSvcAcct&,IWAM_