Automating Maintenance with the ASDK 2 – Merging Duplicate Records

I wrote an article a bit ago on how to automate the deletion of unmanaged machines through the ASDK. I thought today, I would take a little time and write a follow up article to cover automating another maintenance related topic. What I plan on covering in this article is how to write a simple application that will automate the merging of duplicate records. So what are duplicate records? In my organization, we typically see three kinds. We see duplicate owners where there are two users/owners that are the same person, but maybe changed domains, resulting in a second record being created, one for the old domain, and one for the new. Next, we see duplicate serial numbers where the same asset will show up twice. Each will have its own unique GUID, but the serial numbers will obviously be the same. Finally, duplicate names. This is similar to the serial number issue. There will be two records for the same machine, with the same name, but with their own unique GUID. Merging them allows us to combine them into a single record, keeping the latest information about the asset.

Where to Begin

The first step in using any portion of the SDK is to install it on your Notification or Asset server so the methods we need will be available for use. The ASDK can be found here: http://altirisdl.com/eval/Altiris_ASDK_1_4.exe. The module that we will be using specifically is:
 
ResourceManagementService – This is found in the Notification Server portion of the SDK. References to its methods can be found in the help file under Altiris ASDK > API Reference > Notification Server > Altiris.ASDK.NS > ResourceManagementLib Class > Methods. The method that we will be using is the MergeResource Method. We will use this to merge duplicate owners, serial numbers, and assets with the same name.

Once the SDK is installed on your Notification and or Asset Server, there are a couple of ways that we can begin. We can either use the compiled DLLs that Altiris provides, which works just fine, or we can create a proxy class, which is essentially an uncompiled version of the DLL that is provided by Altiris. I prefer to use the proxy class so all my code can be compiled into one executable, where as with the DLL, you have to distribute the DLL with your exe for it to function. For this example, we will be creating and using a proxy class, but I will note along the way where you can use the DLL instead, if that is what you prefer.

Creating Your Proxy Class

The first thing you will need for all of this is a copy of Visual Studio. You can use the free express edition if you like that are available through the Microsoft Site: http://www.microsoft.com/Express. I will be doing my development in C#. Once you have Visual Studio installed, open up the Visual Studio Command Prompt. If you are using 2008, it can be found here Start > Programs > Microsoft Visual Studio 2008 > Visual Studio Tools > Visual Studio 2008 Command Prompt. When the command prompt opens you will run the following command (make sure to populate the name of your server that you just installed the SDK on):

wsdl /language:CS http://<your server>/Altiris/ASDK.NS/ ResourceManagementService.asmx

So, for example, my command would be:

wsdl /language:CS http://its-altirisns/Altiris/ASDK.NS/ResourceManagementService.asmx

If you are using 2008, the proxy classes are created and placed by default here: C:\Program Files\Microsoft Visual Studio 9.0\VC. If you browse to this location you will now see a new file titled ItemManagementService.cs. This will be used in your Visual Studio project.

Creating your Project

Now that we have our proxy classes created, launch Visual Studio and create a new project. This can be either a Console application or a Windows Forms application. For the purpose of this article, I will be using a Windows Forms application.

Now the first thing that we need to do is add a couple of references and using statements.
Replace the list of using statements at the top of your project with the following list:

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Net;

Now add a new reference to System.Web.Services. This is done by going up to the menu at the top and clicking Project > Add Reference. Now under the .NET tab, select System.Web.Services and click OK.

***NOTE***
If you are not using the proxy classes, but instead you are using the DLLs, you will also need to add a reference to the Altiris.ASDK.NS.COM.dll. This lives out on your Notification server here: Program Files\Altiris\Altiris ASDK\COM Assemblies\Altiris.ASDK.NS.COM.dll. You will also have to add the following using statement to the top of your code if you are using the DLL:
using Altiris.ASDK.NS.COM.ResourceManagementProxy;

Next, we will import the ResourceManagementService class that we created earlier. To import the proxy class into your project, simply drag and drop it from where it currently lives in program files, into your solution explorer window and onto your project:

Next, lets add a button to our form so we have something we can click to test our code. Once everything is working properly, we can easily turn this into an application that runs from command line and take arguments for any variables that we need to fill where no button clicks are needed.
Next we will need to pull data from our Altiris SQL Database to determine records need to be merged. This will require a new SQL connection. In the click event for your button, create your SQL connection. This can be done a number of ways, but for this demonstration, I have created my connection as follows:

//CREATE AND OPEN CONNECTION TO SQL DATABASE
var con = new SqlConnection(@"Trusted_Connection=true; Data Source=SQL03T; Initial Catalog=Altiris; Integrated Security=true");
con.Open();

Now we need to provide it with the SQL command that will be used to retrieve duplicates for merging. I will be taking the results from our query and storing them in a DataTable. The SQL query that we will use is. So what type of duplicates are we looking for? Some common choices would be Serial Numbers, Names, or Owners/Users, so I will cover the SQL for all three of those and then we will make our application dynamic enough to where it can handle any of the three.

Here is the SQL code for the three queries that we will be doing. Each one will return any duplicates that are found in this table format:

-----------------------------------------------------------------
| Old Asset | Old GUID | New Asset | New GUID |
-----------------------------------------------------------------
------------------------------------------------------
--DUPLICATE SERIAL NUMBERS
------------------------------------------------------
With dups([Asset],[Guid],[Days]) AS
(Select 	v.[Serial Number],
	v.[_ResourceGuid],
	Max(DateDiff(n,isnull(i.[Client Date],01/01/1999),GetDate()))'Days'
From Inv_AeX_HW_Serial_Number v
Left Join Inv_AeX_AC_Identification i
on v.[_ResourceGuid]= i.[_ResourceGuid]
WHERE 	v.[Serial Number] <> '' AND
	v.[Serial Number] <> 'Not Found' AND
	v.[Serial Number] In
	(SELECT [Serial Number]
	From Inv_AeX_HW_Serial_Number
	Group By [Serial Number]
	HAVING COUNT([Serial Number])>1)
Group By v.[Serial Number],v.[_ResourceGuid])
Select * From (Select [Asset]'Old Asset', [Guid]'Old Guid'
	from dups d where days = (select max(days) from dups d2
		where d2.[Asset] = d.[Asset]))o
Left Join (Select [Asset]'New Asset', [Guid]'New Guid'
	from dups d where days = (select min(days) from dups d2
		where d2.[Asset] = d.[Asset]))n
On o.[Old Asset] = n.[New Asset]
Where o.[Old Guid] <> n.[New Guid]
------------------------------------------------------
--DUPLICATE OWNERS
------------------------------------------------------
With dups([Asset],[Guid],[Days]) AS
(Select 	v.[Name],
	v.[Guid],
	Max(DateDiff(n,isnull(i.[ModifiedDate],01/01/1999),GetDate()))'Days'
From vUser v
Left Join item i on v.[Guid]= i.[Guid]
WHERE v.[Name] <> '' AND
v.[Name] In
	(SELECT [Name]
	From vUser
	Group By [Name]
	HAVING COUNT([Name])>1)
Group By v.[Name],v.[Guid])
Select * From (Select [Asset]'Old Asset', [Guid]'Old Guid'
	from dups d where days = (select max(days) from dups d2
		where d2.[Asset] = d.[Asset]))o
Left Join (Select [Asset]'New Asset', [Guid]'New Guid'
	from dups d where days = (select min(days) from dups d2
		where d2.[Asset] = d.[Asset]))n
On o.[Old Asset] = n.[New Asset]
Where o.[Old Guid] <> n.[New Guid]
------------------------------------------------------
--DUPLICATE NAMES
------------------------------------------------------
With dups([Asset],[Guid],[Days]) AS
(Select 	v.[Name],
	v.[_ResourceGuid],
	Max(DateDiff(n,isnull(i.[Client Date],01/01/1999),GetDate()))'Days'
From Inv_AeX_AC_Identification v
Left Join Inv_AeX_AC_Identification i
on v.[_ResourceGuid]= i.[_ResourceGuid]
WHERE v.[Name] <> '' AND
v.[Name] In
	(SELECT [Name]
	From Inv_AeX_AC_Identification
	Group By [Name]
	HAVING COUNT([Name])>1)
Group By v.[Name],v.[_ResourceGuid])
Select * From (Select [Asset]'Old Asset', [Guid]'Old Guid'
	from dups d where days = (select max(days) from dups d2
		where d2.[Asset] = d.[Asset]))o
Left Join (Select [Asset]'New Asset', [Guid]'New Guid'
	from dups d where days = (select min(days) from dups d2
		where d2.[Asset] = d.[Asset]))n
On o.[Old Asset] = n.[New Asset]
Where o.[Old Guid] <> n.[New Guid]

So applying the SQL above, starting with Serial Number and storing the results into a DataTable would look something like this:

//CREATE AND OPEN CONNECTION TO SQL DATABASE
var con = new SqlConnection(@"Trusted_Connection=true; Data Source=SQL03T; Initial Catalog=Altiris; Integrated Security=true");
con.Open();



//CREATE COMMAND AND DEFINE COMMAND TEXT (SQL QUERY THAT WILL BE RUN)
var cmd = con.CreateCommand();
cmd.CommandText = "With dups([Asset],[Guid],[Days]) AS " +
		"(Select v.[Serial Number], " +
		" v.[_ResourceGuid], " +
		"Max(DateDiff(n,isnull(i.[Client Date],01/01/1999),GetDate()))'Days' " +
		"From Inv_AeX_HW_Serial_Number v " +
		"Left Join Inv_AeX_AC_Identification i " +
		"on v.[_ResourceGuid]= i.[_ResourceGuid] " +
		"WHERE v.[Serial Number] <> '' AND " +
		"v.[Serial Number] <> 'Not Found' AND " +
		"v.[Serial Number] In " +
		"(SELECT [Serial Number] " +
		"From Inv_AeX_HW_Serial_Number " +
		"Group By [Serial Number] " +
		"HAVING COUNT([Serial Number])>1) " +
		"Group By v.[Serial Number],v.[_ResourceGuid]) " +
		"Select * From (Select [Asset]'Old Asset', [Guid]'Old Guid' " +
		"from dups d where days = (select max(days) from dups d2 " +
		"where d2.[Asset] = d.[Asset]))o " +
		"Left Join (Select [Asset]'New Asset', [Guid]'New Guid' " +
		"from dups d where days = (select min(days) from dups d2 " +
		"where d2.[Asset] = d.[Asset]))n " +
		"On o.[Old Asset] = n.[New Asset] " +
		"Where o.[Old Guid] <> n.[New Guid]";


//CREATE A DATA READER THAT WILL PULL BACK THE RESULTS
var dr = cmd.ExecuteReader();



//POPULATE A DATA TABLE WITH THE INFORMATION THE DATA READER PULLS BACK
var dt = new DataTable("Duplicates");
if (dr == null) return;
dt.Load(dr);
var dv = dt.DefaultView;



//CLOSE AND DISPOSE OF THE DATA READER AND CONNECTION
dr.Close();
dr.Dispose();
con.Close();
con.Dispose();

By stepping through your code in Debug mode, you should easily be able to see if your query was successful. If it was, you will see something like this stored in the contents of your DataTable:

Now we have the data, we just need to do something with it. This will be done using the proxy class that we created earlier. So the first step is to create a new ResourceManagementService object. Along with this, there are a couple of properties that we need to set. First we need to set the URL property. By default, the URL will be exactly the same as the one that you used to create the class, so something like this: http://<your server>/Altiris/ASDK.NS/ ResourceManagementService.asmx. If this is correct, you will not need to set the URL. If by chance, like me, you have more than one server you want to work with, instead of creating a different proxy class for each one, you can just repoint the URL property of the one you’ve created. I am going to repoint mine to my test server (I would highly recommend running anything you develop on a test server prior to using it in production!). The other piece we need to take care of is defining what credentials will be used when running the deletion method. You can set specific credentials, or you can use the default credentials that the application is being run under, which is what I will be doing. Once the object is created, the URL is re-pointed, and the credentials are set, your next little snippet of code will look something like this:

//CREATE A NEW ResourceManagementService OBJECT AND SET PROPERTIES
var rms = new ResourceManagementService
{
	Url = "http://itsaltiristesta/Altiris/ASDK.NS/ResourceManagementService.asmx ",
	Credentials = CredentialCache.DefaultCredentials
};

Now all that is left is to step through your table of duplicates and feed each one to the MergeResource method of your ResourceManagementService object. The MergeResource method requires two GUIDs for inputs. The first will be the old record that is going to go away, the second GUID is the new GUID that the old one will be merged with.This can be done through a simple foreach statement.

So the snippet of code for stepping through your table would look like this:

//STEP THROUGH TABLE AND MERGE OLD WITH NEW FOR EACH RECORD foreach(DataRow row in dv.Table.Rows)
{
	var oldGuid = row[1].ToString();
	var newGuid = row[3].ToString();
	rms.MergeResource(oldGuid, newGuid);
}

At this point, clicking your button should successfully begin merging duplicate records in your NS, but as I mentioned above, the goal is to automate the process so you don’t have to hire a button pusher just to keep your database clean. The best way I have found to automate the task our code performs is to either use a console application, or to move our code out of our click event and into our form’s load event. Then I would replace a couple of things with variables, like SQL server, and Notification Server so you could use the same compiled EXE with any server and database that you like. The other thing that you have to remember to do if you leave your code in a Windows form application is to make sure that your application exits when it is done with its work. By default, a form will just stay open until it is told to close, where as a console application will run through its code and quit when it is done. For the sake of consistency, I will continue on with the forms application that we have been using to this point. 

Below is what the code would look like if I were to convert the server names to variables that are passed in through command line. I have also turned a number of pieces of the SQL query into variables so that I can use the same SQL query for all three types of duplicates based off of the argument that is passed in. Next, I have put in some code so it will write errors out to a log file in case a merge task fails. I have seen this happen if there are two assets with the same serial, but they are different types, like a monitor and computer got created with the same serial by mistake. Finally I have hidden the form since it now needs no input from the user, and put in a couple of exit events where they are needed. I have also moved my code to the form’s load event and out of the button’s click event.

Final code:

//HIDE THE FORM SINCE THERE IS NO NEED TO SEE IT ANYMORE
WindowState = FormWindowState.Minimized;
Visible = false;

//GET COMMAND LINE ARGUMENTS THAT ARE PASSED IN
var cmdLine = Environment.GetCommandLineArgs();

//CODE TO DISPLAY HELP BOX ON REQUIRED ARGUMENTS
if (cmdLine.Length == 2)
if (cmdLine[1] == "/?")
{
	MessageBox.Show("Required Command Lines: \r\n" +
	"/sqlServer=<your sql server>\r\n" +
	"/altirisServer=<your altiris server>\r\n" +
	"/type=<type of duplicates to merge>\r\n" +
	"Avaialble types:owner, serial, name\r\n" +
	"/log=<unc path to store errorlog>\r\n" +
	"-----------------------------------------\r\n" +
	"/? - brings up this menu.", "Command Line Options", MessageBoxButtons.OK, MessageBoxIcon.Information);
	Environment.Exit(0);
}

//VARIABLES THAT COMMANDLINES WILL BE STORED IN
var sqlServer = "";
var altirisServer = "";
var type = "";
var log = "";
if (cmdLine.Length == 5)
{
	//PARSE THE COMMAND LINE
	var cmd1Split = cmdLine[1].Split(new[] { '=' });
	var cmd1Type = cmd1Split[0].Trim().ToUpper();
	var cmd1Value = cmd1Split[1].Trim().ToUpper();
	var cmd2Split = cmdLine[2].Split(new[] { '=' });
	var cmd2Type = cmd2Split[0].Trim().ToUpper();
	var cmd2Value = cmd2Split[1].Trim().ToUpper();
	var cmd3Split = cmdLine[3].Split(new[] { '=' });
	var cmd3Type = cmd3Split[0].Trim().ToUpper();
	var cmd3Value = cmd3Split[1].Trim().ToUpper();
	var cmd4Split = cmdLine[4].Split(new[] { '=' });
	var cmd4Type = cmd4Split[0].Trim().ToUpper();
	var cmd4Value = cmd4Split[1].Trim().ToUpper();
	
	//STORE THE CORRECT COMMAND LINE IN THE CORRECT VARIABLE
	if (cmd1Type == "/SQLSERVER")
		sqlServer = cmd1Value;
	else if (cmd1Type == "/ALTIRISSERVER")
		altirisServer = cmd1Value;
	else if (cmd1Type == "/TYPE")
		type = cmd1Value;
	else if (cmd1Type == "/LOG")
		log = cmd1Value;
	
	if (cmd2Type == "/SQLSERVER")
		sqlServer = cmd2Value;
	else if (cmd2Type == "/ALTIRISSERVER")
		altirisServer = cmd2Value;
	else if (cmd2Type == "/TYPE")
		type = cmd2Value;
	else if (cmd2Type == "/LOG")
		log = cmd2Value;
			
	if (cmd3Type == "/SQLSERVER")
		sqlServer = cmd3Value;
	else if (cmd3Type == "/ALTIRISSERVER")
		altirisServer = cmd3Value;
	else if (cmd3Type == "/TYPE")
		type = cmd3Value;
	else if (cmd3Type == "/LOG")
		log = cmd3Value;
	
	if (cmd4Type == "/SQLSERVER")
		sqlServer = cmd4Value;
	else if (cmd4Type == "/ALTIRISSERVER")
		altirisServer = cmd4Value;
	else if (cmd4Type == "/TYPE")
		type = cmd4Value;
	else if (cmd4Type == "/LOG")
		log = cmd4Value;	
}


//IF ANY COMMAND LINES WERE LEFT BLANK, EXIT
if (sqlServer == "" || altirisServer == "" || type == "" || log == "")
{
	Environment.Exit(1);
}


//DECLARE VARIABLES THAT WILL BE USED IN THE SQL STATEMENT
var duplicateType = "";
var queryTable = "";
var joinOn1 = "";
var joinOn2 = "";
var joinTo = "";
var dateField = "";
var whereClause = "";

//DUPLICATE SERIAL IN THE INV_AEX_HW_SERIAL_NUMBER TABLE
if (type.ToUpper() == "SERIAL")
{
	duplicateType = "[Serial Number]";
	queryTable = "Inv_AeX_HW_Serial_Number";
	joinTo = "Inv_AeX_AC_Identification";
	joinOn1 = "[_ResourceGuid]";
	joinOn2 = "[_ResourceGuid]";
	dateField = "[Client Date]";
	whereClause = "AND v." + duplicateType + " <> 'Not Found' AND ";
}


//DUPLICATE SERIAL IN THE VASSET TABLE
if (type.ToUpper() == "SERIAL2")
{
	duplicateType = "[Serial Number]";
	queryTable = "vAsset";
	joinTo = "item";
	joinOn1 = "[_ResourceGuid]";
	joinOn2 = "[Guid]";
	dateField = "[ModifiedDate]";
	whereClause = "AND v." + duplicateType + " <> 'Not Found' AND ";
}


//DUPLICATE OWNER
if (type.ToUpper() == "OWNER")
{
	queryTable = "vUser";
	duplicateType = "[Name]";
	joinOn1 = "[Guid]";
	joinOn2 = "[Guid]";
	joinTo = "item";
	dateField = "[ModifiedDate]";
	whereClause = " AND ";
}


//DUPLICATE NAMES
if (type.ToUpper() == "NAME")
{
	queryTable = "Inv_AeX_AC_Identification";
	duplicateType = "[Name]";
	joinOn1 = "[_ResourceGuid]";
	joinOn2 =	 "[_ResourceGuid]";
	joinTo = "Inv_AeX_AC_Identification";
	dateField = "[Client Date]";
	whereClause = " AND ";
}


//IF INVALID TYPE WAS PASSED IN, DUPLICATETYPE WILL BE EMPTY, SO EXIT.
if (duplicateType == "") Environment.Exit(1);
//CREATE AND OPEN CONNECTION TO SQL DATABASE
var con = new SqlConnection(@"Trusted_Connection=true; Data Source=" + sqlServer + "; Initial Catalog=Altiris; Integrated Security=true");
//CREATE COMMAND AND DEFINE COMMAND TEXT (SQL QUERY THAT WILL BE RUN)
var cmd = con.CreateCommand();
cmd.CommandText = "With dups([Asset],[Guid],[Days]) AS " +
		"(Select v." + duplicateType + ",v." + joinOn1 + ", " +
		"Max(DateDiff(n,isnull(i." + dateField + ",01/01/1999),GetDate()))'Days' " +
		"From " + queryTable + " v " +
		"Left Join " + joinTo + " i " +
		"on v." + joinOn1 + "= i." + joinOn2 + " " +
		"WHERE v." + duplicateType + " <> '' " + whereClause +
		"v." + duplicateType + " In " +
		"(SELECT " + duplicateType + " " +
		"From " + queryTable + " " +
		"Group By " + duplicateType + " HAVING COUNT(" + duplicateType + ")>1) " +
		"Group By v." + duplicateType + ",v." + joinOn1 + ") " +
		"Select * From " +
		"(Select [Asset]'Old Asset',[Guid]'Old Guid' from dups d " +
		"where days = (select max(days) from dups d2 where d2.[Asset] = d.[Asset]))o " +
		"Left Join " +
		"(Select [Asset]'New Asset',[Guid]'New Guid' from dups d " +
		"where days = (select min(days) from dups d2 where d2.[Asset] = d.[Asset]))n " +
		"On o.[Old Asset] = n.[New Asset] " +
		"Where o.[Old Guid] <> n.[New Guid]";
con.Open();

//CREATE A DATA READER THAT WILL PULL BACK THE RESULTS
var dr = cmd.ExecuteReader();
//POPULATE A DATA TABLE WITH THE INFORMATION THE DATA READER PULLS BACK
var dt = new DataTable("Duplicates");
if (dr == null) Environment.Exit(0);
dt.Load(dr);
var dv = dt.DefaultView;
//CLOSE AND DISPOSE OF THE DATA READER AND CONNECTION
dr.Close();
dr.Dispose();
con.Close();
con.Dispose();
//CREATE A NEW ResourceManagementService OBJECT AND SET PROPERTIES
var rml = new ResourceManagementService
{
	Url = "http://"+ altirisServer + "/Altiris/ASDK.NS/ResourceManagementService.asmx ",
	Credentials = CredentialCache.DefaultNetworkCredentials
};



//STEP THROUGH TABLE AND MERGE OLD WITH NEW FOR EACH RECORD
for (var i = 0; i < dv.Count; i++)
{
	var oldRecord = dv[i][1].ToString();
	var newRecord = dv[i][3].ToString();
	try
	{
		rml.MergeResource(oldRecord, newRecord);
	}
	catch (Exception)
	{
		var sw = new StreamWriter(log, true);
		try
		{
			sw.WriteLine("Select * From " + queryTable +
			" Where " + joinOn1 + " = '" + oldRecord +
			"' OR " + joinOn1 + " = '" + newRecord +
			"' --" + altirisServer);
		}
		catch (Exception)
		{
		}
	}	
}
Environment.Exit(0);

The task can now be run like this, passing the arguments to it and works for three different duplicate types and on any server.
That is it for this article.

As you can see, we have just automated multiple tasks that can be quite time consuming with some creativity and a couple hundred lines of code.