Friday 20 March 2009

What Primary Lookup Key should I use with Deployment Server?

If you choose a Primary Lookup Key that is identical on two or more of your computers or if that key is missing on any of your computers, Deployment Server will not be able to identify them. To identify which Primary Lookup Key is best, open SQL Query Analyzer, select to the Deployment Server database, and run the the query against the Deployment Server database. The result will tell you which keys have duplicate computers and which ones don't. It will also show you which keys have duplicates.

DECLARE @sn INT SET @sn=(SELECT COUNT(1) FROM (SELECT T.serial_num AS [Serial Number], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, serial_num FROM computer GROUP BY serial_num HAVING COUNT(1) > 1) T WHERE C.serial_num = T.serial_num) T )
DECLARE @at INT SET @at=(SELECT COUNT(1) FROM (SELECT T.asset_tag AS [Asset Tag], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, asset_tag FROM computer GROUP BY asset_tag HAVING COUNT(1) > 1) T WHERE C.asset_tag = T.asset_tag) T )
DECLARE @uuid INT SET @uuid=(SELECT COUNT(1) FROM (SELECT T.uuid AS [UUID], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, uuid FROM computer GROUP BY uuid HAVING COUNT(1) > 1) T WHERE C.uuid = T.uuid) T )
DECLARE @mac INT SET @mac=(SELECT COUNT(1) FROM (SELECT T.mac_addr AS [MAC Address], T.duplicates AS [Duplicates], N.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM nics N, (SELECT COUNT(1) AS duplicates, mac_addr FROM nics GROUP BY mac_addr HAVING COUNT(1) > 1) T, computer C WHERE N.mac_addr = T.mac_addr AND C.computer_id = N.computer_id) T )

IF @sn = 0
  SELECT 'Serial Number OK' AS Message, 'You may use Serial Number as your Primary Lookup Key' AS Details
ELSE
  SELECT 'You have computers with duplicate Serial Numbers' AS Warning, 'Do not use Serial Number as your Primary Lookup Key' AS Details
IF @at = 0
  SELECT 'Asset Tag OK' AS Message, 'You may use Asset Tag as your Primary Lookup Key' AS Details
ELSE
  SELECT 'You have computers with duplicate Asset Tags' AS Warning, 'Do not use Asset Tag as your Primary Lookup Key' AS Details
IF @uuid = 0
  SELECT 'UUID OK' AS Message, 'You may use UUID as your Primary Lookup Key' AS Details
ELSE
  SELECT 'You have computers with duplicate UUIDs' AS Warning, 'Do not use UUID as your Primary Lookup Key' AS Details
IF @mac = 0
  SELECT 'MAC Address OK' AS Message, 'You may use MAC Address as your Primary Lookup Key' AS Details
ELSE
  SELECT 'You have computers with duplicate MAC Addresses' AS Warning, 'Do not use MAC Address as your Primary Lookup Key' AS Details

SELECT T.serial_num AS [Serial Number], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, serial_num FROM computer GROUP BY serial_num HAVING COUNT(1) > 1) T WHERE C.serial_num = T.serial_num
SELECT T.asset_tag AS [Asset Tag], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, asset_tag FROM computer GROUP BY asset_tag HAVING COUNT(1) > 1) T WHERE C.asset_tag = T.asset_tag
SELECT T.uuid AS [UUID], T.duplicates AS [Duplicates], C.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM computer C, (SELECT COUNT(1) AS duplicates, uuid FROM computer GROUP BY uuid HAVING COUNT(1) > 1) T WHERE C.uuid = T.uuid
SELECT T.mac_addr AS [MAC Address], T.duplicates AS [Duplicates], N.computer_id AS [Computer ID], C.name AS [Console Name], C.computer_name AS [Computer Name] FROM nics N, (SELECT COUNT(1) AS duplicates, mac_addr FROM nics GROUP BY mac_addr HAVING COUNT(1) > 1) T, computer C WHERE N.mac_addr = T.mac_addr AND C.computer_id = N.computer_id

No comments:

Post a Comment