One of the really exciting features that were introduced in Asset 6.5 is the ability to run preprocessing functions on the data that you import through connector solution. Prior to 6.5, any formatting had to be done prior to import either manually or through scripting. Here is an example for those not as familiar with connector solution. We are a Dell shop and every morning, we receive an automated report from Dell with everything that is to be shipped that day. We then take that data and import it into our asset system, which allows us to create records in our database, and start the asset life cycle started prior to the asset ever reaching our docks.
Pulling in data from these reports, however, presented some challenges. These reports contain data on several types of assets; computers, printers, ink cartridges, keyboards, mice, etc. The only ones we are interested in tracking in our asset system are computers and printers, so needless to say, some clean up had to be done before each import. That was the first challenge.
Next, since computers and printers are contained in the same file, we couldn’t just run an import job because the system wasn’t smart enough to separate computers and printers and create the appropriate asset type as each record was processed.
Finally, we wanted to add a column to the spreadsheets that were sent to us which contained the status of these new assets. We have a custom status called “Dell Import” which lets us know that these records were created as a result of an import job, and once they arrive and are scanned and deployed, they go out of the “Dell Import” status and into “Active”. Prior to Asset 6.5, we handled these challenges through various scripts and semi-complicated work-arounds.
With the release of Asset 6.5, this all became much easier. The first thing that was introduced in 6.5 was the ability to import multiple resource types with a single job. This allows you to specify a column in your import data that specifies which type of asset is to be created, either by asset type name, or asset type GUID. What this meant in our scenario above was that now we are able to import printers and computers with a single job as long as we have a column in the data stating what type of asset it is. This is done in your import/export rule by checking the “Multiple resource type import” box and then setting the value of the Resource Type drop down to point to the column in your data that specifies what type of asset it is.
The second feature that was added in Asset 6.5 that this article will focus specifically on is the ability to do pre-processing to the import data. What this means to us is we can pull in our Dell report just as it is without any scripts or workarounds performing the clean up functions needed to get our data into the system in the format that we want it. So, let’s begin. I will recreate our Dell import job from scratch so you can see all the steps from start to finish.
First, we need to create a new Data Source. Data sources can be a text file, CSV, SQL Database, or in our case, an Excel spreadsheet. To create a new data sources, go to Configuration > Solution Settings > Connectors > Data Sources.
Right click on the Data Sources folder, select New, and then the type of data source you would like to create. In my case, since I am using an Excel spreadsheet, I will select “OleDB Data Source”. Fill in all the information for the new Data Source and check both the “Allow Imports” and the “Enable import data pre-processing function” check boxes.
To add a pre-processing function, click the Edit function link next to the “Enable import data pre-processing function” check box. The following screen will be displayed:
Pre-processing functions are written in C#. Now, if you don’t know C#, don’t worry, basic data manipulation is not too difficult, and I will cover some basics as we go through our example. If you do know C#, you will find that there is very little you cannot do with your data prior to importing it. One thing to note, the length of you functions is limited to 2046 characters, but that should be more than enough room for most anything you want to do.
To start, let’s look at what is displayed in this pre-processing window. The first section at the top lists the code namespaces that are available for use:
using System;
using System.Xml;
using System.Collections;
using System.Collections.Specialized;
using System.IO;
using System.Data;
using Altiris.Common;
What are namespaces you might ask? Namespaces are sort of like code libraries that contain functions that are available for use to manipulate your data. What does this mean to you? If you have not done C# coding in the past, it won’t mean a whole lot, and if you have, you will appreciate the wide variety of functions available to you for modifying your data. Either way, I wanted to start at the top and touch on each section so it will not be a mystery to those of you who are new to this.
The next section is the one that we are the most interested in. This is where we will place our code to modify the data that is imported. So let’s go through this one line a time and I will try to break it down into English. This first line, "public class DataSourcePreProcessing", doesn’t matter a whole lot to us. It is basically just telling us that our code is going to be written in the public class “DataSourcePreProcessing”. A class is similar to a namespace. It is a logical grouping of code that contains functions we can use (among other things that are out of scope for this article). You can think of this as a container for our function. Everything that comes between the open and close brackets is held in this container.
Next is our function, “ProcessData” (public DataTable ProcessData ( DataTable importData )). This is where we are going to place our code. Like a class, a function begins and ends with open and close brackets. These brackets are provided for us, but I wanted to mention them just so you understand their purpose.
The two most important pieces of this function declaration are the argument and return type, both of which are DataTable. What this means is that we are starting with a DataTable and we are ending with a DataTable. What we do to it in the middle is up to us. You can think of the initial DataTable (which is titled importData) as a grid, or spreadsheet containing our import data prior to any modifications. In my case, this DataTable contains the original data from our Dell report. The DataTable we return, similarly, can be thought of as a grid or spreadsheet containing our newly modified data.
Now let’s enter the code that will modify our data. This piece will get fairly technical, but I will try to explain everything I do to keep it from getting too confusing. One thing to note, in C# a double slash marks a comment. I will use these in my code to explain what is going on.
The first thing I want to do for my Dell data is to create a new DataTable that will hold my modified data. I want it to have all the same columns as the original data, plus one new column that will hold status information. If you remember, I want to import all the assets off the Dell report under our custom “Dell Import” status so they are easy to find and report on. To do this we will add the following line of code to the pre-processing text box:
//This will create a new data table called “filteredData” to hold our modified data
DataTable filteredData = new DataTable();
//Next, we want to give it all the same columns as our original DataTable.
//This is done by stepping through the columns in the original DataTable,
//And for each one, we will create a like column with the same name in our new DataTable
foreach (DataColumn column in importData.Columns)
filteredData.Columns.Add(column.ColumnName);
//Here, we are taking our new DataTable and adding a new column to it called "Status"
filteredData.Columns.Add("Status");
Now we have a "spreadsheet" like DataTable called filteredData which contains all the same columns as the data we are importing, plus an additional column to hold our status information. Now that our columns are created in our filteredData table, we need to add rows to it. As I mentioned, our Dell report has a lot more information in it than just computers and printers. I want to exclude everything that is not a computer or printer. To do this, I will create a new DataRow array called “assets” which will be our container that holds everything from the original DataTable where the “Category” column is equal to “Imaging” (Dell’s word for Printer), “Notebooks” (Dell’s word for Laptop), or “Desktops”.
//This is an array of data rows that holds all our filtered data
DataRow[] assets = importData.Select("[Category] = 'Imaging' OR [Category] = 'Notebooks' OR [Category] = 'Desktops'");
So that takes care of pulling out only the data we are interested in, but we have two small problems. First, since the assets data row was created from the initial DataTable, it is missing the “Status” column. Next, the Asset Types in Altiris are “Computer” and “Network Printer”, not “Imaging”, “Notebooks”, and “Desktops”, so this needs to be updated. In this next section, we are going to step through the assets array, add the “Status” column, correct the category to match our terminology, and then add the row to our filteredData table.
//Step through each element of the assets DataRow array
foreach(DataRow row in assets) {
//Replace "Imaging" in category column with "Network Printer"
row["Category"] = row["Category"].ToString().Replace("Imaging", "Network Printer");
//Replace "Notebooks" in category column with "Computer"
row["Category"] = row["Category"].ToString().Replace("Notebooks", "Computer");
//Replace "Desktops" in category column with "Computer"
row["Category"] = row["Category"].ToString().Replace("Desktops", "Computer");
//Create a new array for the current row, but make it one column
//longer than the current row so we can insert our status
object[] currentRow = new object[row.ItemArray.Length + 1];
//Take the data held in “row” and copy it to our currentRow array
//starting at position 0 in the array (the first column)
row.ItemArray.CopyTo(currentRow,0);
//We now an array that is long enough to hold all the data from the current row
//and it has been populated with everything except for our status information.
//We will now populate the last column with status
currentRow[currentRow.Length - 1] = "Dell Import";
//Now that our currentRow array is completely populated; we can add it to our
//filteredData table that will contain all our modified data.
filteredData.Rows.Add(currentRow);
}
Now we have our filteredData table exactly how we want it. We took the imported data, we modified it, then we stored it in a new DataTable. All that is left to do is to return it. If you remember earlier, I mentioned, this function takes a DataTable as an argument (or input parameter), and then has to return a DataTable. This is done by typing the following:
return filteredData;
Here is a copy of our final code. I left out the comments to save space.
DataTable filteredData = new DataTable();
foreach (DataColumn column in importData.Columns)
filteredData.Columns.Add(column.ColumnName);
filteredData.Columns.Add("Status");
DataRow[] assets = importData.Select("[Category] = 'Imaging' OR [Category] = 'Notebooks' OR [Category] = 'Desktops'");
foreach (DataRow row in assets)
{
row["Category"] = row["Category"].ToString().Replace("Imaging", "Network Printer");
row["Category"] = row["Category"].ToString().Replace("Notebooks", "Computer");
row["Category"] = row["Category"].ToString().Replace("Desktops", "Computer");
object[] currentRow = new object[row.ItemArray.Length + 1];
row.ItemArray.CopyTo(currentRow, 0);
currentRow[currentRow.Length - 1] = "Dell Import";
filteredData.Rows.Add(currentRow);
}
return filteredData;
Really, not too difficult. Now we can create the import/export rule and pull in our modified data by referencing this data source. In fewer than 20 lines of code we have eliminated complex work-arounds, external scripts, and the need for multiple import jobs.
I hope this has been helpful.