Category: SQL

The organisation i work for has 4 regions, these regions have changed over time as the business has moved and shifted. Meaning we don’t have the 4 region structure replicated in our Active Directory. This causes me alot of headaches when building reports. I am frequently asked for reports containing only data from specific regions – so I needed an easy way to ‘Regionalise’ my queries…

In the past, I’ve used several methods to include either the AD Site or the OU the machine is in. However – these methods have their drawbacks :

• AD Site changes for laptops when they move around
• The table in SCCM which has OU information for a machine contains multiple rows per machine, and our AD structure doesn’t represent the 4 regions – we have 5 top level OU’s

The table which has the OU information for a particular machine is stored in the following format, with a line for each node in the path :

• COMPANY.COM/EUROPE
• COMPANY.COM/EUROPE/COUNTRY
• COMPANY.COM/EUROPE/COUNTRY/OFFICE
• COMPANY.COM/EUROPE/COUNTRY/OFFICE/COMPUTERS

The easiest way I have found to tackle this problem is using a temporary table in the SQL query. We add the top level OU’s to this table so that we can join this table to the rest of the data in the query. If you ammend the below to OU names that match the ones in your SCCM DB you will be able to run it – however if you do, you will notice that we still have multiple rows per machine (which we dont want!)

-- Create Temporary Table to Store the OU's for each 'Region'
CREATE TABLE #Regions(
	RegionID int,
	RegionName NVARCHAR(50),
	RegionOU NVARCHAR(100)
)

-- Insert Data into Temporary Table
INSERT INTO 
	#Regions (RegionID, RegionName, RegionOU)
VALUES
	(0, 'AMX', 'COMPANY.COM/AMERICAS'),
	(1, 'AUS', 'COMPANY.COM/AUSTRALASIA'),
	(2, 'EAR', 'COMPANY.COM/EAST ASIA'),
	(3, 'EMEA', 'COMPANY.COM/EUROPE'),
	(4, 'EMEA', 'COMPANY.COM/AFRICA')

SELECT
	sys.Netbios_Name0 as [Hostname],
	ou.System_OU_Name0 as [OU],
	reg.RegionName

FROM
	v_R_System_Valid as sys
	INNER JOIN
	v_RA_System_SystemOUName as ou
		ON 
			sys.ResourceID = ou.ResourceID
	LEFT OUTER JOIN
	#Regions as reg 
		ON
			ou.System_OU_Name0 = reg.RegionOU

The beauty of doing it this way, is that; the only thing you need to do – to remove the unwanted rows is add a ‘WHERE’ clause to your SQL query.

The final query would look something like this :

-- Create Temporary Table to Store the OU's for each 'Region'
CREATE TABLE #Regions(
	RegionID int,
	RegionName NVARCHAR(50),
	RegionOU NVARCHAR(100)
)

-- Insert Data into Temporary Table
INSERT INTO 
	#Regions (RegionID, RegionName, RegionOU)
VALUES
	(0, 'AMX', 'COMPANY.COM/AMERICAS'),
	(1, 'AUS', 'COMPANY.COM/AUSTRALASIA'),
	(2, 'EAR', 'COMPANY.COM/EAST ASIA'),
	(3, 'EMEA', 'COMPANY.COM/EUROPE'),
	(4, 'EMEA', 'COMPANY.COM/AFRICA')

SELECT
	sys.Netbios_Name0 as [Hostname],
	ou.System_OU_Name0 as [OU],
	reg.RegionName

FROM
	v_R_System_Valid as sys
	INNER JOIN
	v_RA_System_SystemOUName as ou
		ON 
			sys.ResourceID = ou.ResourceID
	LEFT OUTER JOIN
	#Regions as reg 
		ON
			ou.System_OU_Name0 = reg.RegionOU
WHERE 
-- This clause removes unwanted rows
	reg.RegionOU IS NOT NULL

-- These lines are required to drop (remove) the temporary table now that we have finished with it. If you are working on a query and it doesnt fully execute, you may be left in a situation where the temporary table is not removed.
GO
DROP TABLE #Regions