Querying Server Configuration from the MOM 2005 Operations DB, Part 2

Okay, to finish up on our discussion yesterday, MOM's Onepoint database contains quite a bit of information on the characteristics of your agent-managed servers. Clearly, the right way to manage and report on server configuration is through a systems management package like SMS. But for the underprivileged, MOMma will do in a pinch. Now this query performs a couple of joins to pull this data out. for a given server. To display characteristics for a given server contained in multiple database records in a single line of output, you have to go one of two ways. Most people use # temp tables to accomplish this, which is the wrong way to go. Derived tables is the new black, and holds several advantages over temp tables, including 1) it offers much better performance, because the derived tables are stored in memory and 2) It is SQL Reporting Services friendly. It takes building a stored procedure to get SQL Reporting Services to swallow a temp table. So if you're an SRS neophite, get comfy with derived tables...if you're old school and using temp tables, consider switching. Find a tutorial on derived tables in TSQL HERE. And here's your sample query, with some quick comments on what's happening (run against Onepoint). This returns server name, IP address, time zone bias and Active Direcory Site (this last column requires you follow steps HERE first). Dig around and see what other attributes you can find of interest to you. Given we avoided temp tables, you can turn this into a MOM Report with minimal effort. --First derived table (aliased as a1) returns IP addresses --Notice the left outer join - outer joins ensure we get a list of all agent-managed server even --if we have null values in some columns select DISTINCT a1.Server,a1_IP as [IP Address],b1_OS as [OS Version], c1_TimeZoneBias as [Time Zone Bias], d1_ADSiteName as [AD Site], e1_Misc from (select DISTINCT b.[Name] as Server,c.Value as a1_IP from Instance a left outer join computer b on a.instanceID = b.idComputer left outer join attribute c on a.instanceID = c.instanceID left outer join classattribute d on c.classattributeID = d.classattributeID where d.ClassAttributeName = 'IPAddress' and c.Value is not NULL Group by b.[Name], c.Value) AS a1 --2nd derived table (aliased as b1) gets OS current version left outer JOIN (select DISTINCT b.[Name] as Server,c.Value as b1_OS from Instance a left outer join computer b on a.instanceID = b.idComputer left outer join attribute c on a.instanceID = c.instanceID left outer join classattribute d on c.classattributeID = d.classattributeID where d.ClassAttributeName = 'Windows Current Version' Group by b.[Name], c.Value) AS b1 on a1.Server = b1.Server --3rd (c1) gets time zone bias, and so on...you get the point. left outer JOIN (select DISTINCT b.[Name] as Server,c.Value as c1_TimeZoneBias from Instance a left outer join computer b on a.instanceID = b.idComputer left outer join attribute c on a.instanceID = c.instanceID left outer join classattribute d on c.classattributeID = d.classattributeID where d.ClassAttributeName = 'Time Zone Bias' Group by b.[Name], c.Value) AS c1 on a1.Server = c1.Server left outer JOIN (select DISTINCT b.[Name] as Server,c.Value as d1_ADSiteName from Instance a left outer join computer b on a.instanceID = b.idComputer left outer join attribute c on a.instanceID = c.instanceID left outer join classattribute d on c.classattributeID = d.classattributeID where d.ClassAttributeName like '%Active Directory Site Name%' Group by b.[Name], c.Value) AS d1 on a1.Server = d1.Server