I recently had a need for some distinct data. Now, usually that’s simple enough. We simply create a data reader or dataset or whatever and run a query against our database and life is good. Unfortunately, my data source this time around was not SQL based but instead, was coming from the System Center Operations Manager 2007 SDK.

My hopes was, query SCOM for all of the active alerts and populate a pull down with all of the distinct alerts so I could do a mass close when someone generates several thousand alerts for no apparent reason. It really does happen more often than you think. Just the other day, someone ran a script in our lab that generated over 3000 email user accounts. This generated over 3000 alerts to our security group who ensures that all user accounts are created by authorized people only.

So how do we do this? If I have 3000 alerts for account creation, I only want to see the title of one of these alerts. I started by creating a data table. A data table is customizable and easily populated. I only need the alert name so I only need one field in my table.

DataTable dt = new DataTable(“SCOMAlerts”);
DataColumn dc = new DataColumn();
DataRow dr = new DataRow();

dc.ColumnName = “AlertName”;
dt.Columns.Add(dc);

                Ok, the table is built and we have our column. The next part is a lot of code so I’m going to skip right to the point where I load the table…

…

foreach(MonitoringAlert a in alerts)
{
    if(a.ResolutionState != closedState.ResolutionState)
    {
        dr = dt.NewRow();
        dr[0] = a.Name;
        dt.Rows.Add(dr);
    }
}

Now comes the fun part. We have a data table and we have data in our data table. Now we need to filter out all of the duplicate rows from our table.

We will use LINQ for this. Make sure you have a using directive for System.Linq (HINT: it is added to all .NET 3.x projects by default)

Now we create an enumerable string query.

IEnumerable<string> query = (from row in dt.AsEnumerable()
                                                      select row.Field<string>(“AlertName”)).Distinct();

Now we can loop through them and display the data

foreach(string row in query)
{
    Console.WriteLine(row);
}

Of course, you can do anything you want with that row, I have it populating a ComboBox control. The method I use to do that is an article all of its own for another day.

I hope you find this useful!

Ron Hagerman