<tap><tap><tap>

Hello?? Is this thing ON??  

OK, so I've been harassed by a lot of folks "out there" for a LONG time to get into the blogosphere, so What the Hey! Let's DO this, and have some fun while we're at it!!!

I recently had a customer request a procedure to pull out of the database all the machines in the NightWatchman Console, organized by their GROUP assignments. The intent was to hand off all the machines in a specific group to their LanDesk folks to use for targeting a software distribution. Anyone that knows me also knows that I am not a SQL whiz, so my first stop was to ask someone who is! Reto Egeter in our Support Team in NYC is nothing short of amazing. Within 10 minutes of my request, he provided me the following SQL query. Simply open SQL Server Management Studio, open a New Query window, paste it in and let it fly:

USE  AgilityFrameworkreporting
SELECT [DomainName], [NetbiosName], LastPolicy, MAX(Department) AS Department, MAX(Location) AS Location
FROM(SELECT [DomainName]      ,[NetbiosName]      ,MAX([LastPolicyRequestedTime])
AS LastPolicy        ,tbAFR_Dimension_Tier.Name
AS Department        ,NULL
AS Location 
FROM [tbAFR_Dimension_ConfigurationItem]INNER JOIN tbAFR_Dimension_SystemLocator
ON tbAFR_Dimension_SystemLocator.ConfigItemId = [tbAFR_Dimension_ConfigurationItem].Id

INNER JOIN tbAFR_Dimension_Tier
ON tbAFR_Dimension_Tier.Id = tbAFR_Dimension_SystemLocator.TierId
WHERE TierLevelId = 2010
GROUP BY [DomainName], [NetbiosName], tbAFR_Dimension_Tier.Name 

UNION ALL 

SELECT [DomainName]      ,[NetbiosName]      ,MAX([LastPolicyRequestedTime]) AS LastPolicy        ,NULL
AS Department        ,tbAFR_Dimension_Tier.Name AS Location  FROM [tbAFR_Dimension_ConfigurationItem]
INNER JOIN tbAFR_Dimension_SystemLocator
ON tbAFR_Dimension_SystemLocator.ConfigItemId = [tbAFR_Dimension_ConfigurationItem].Id
INNER JOIN tbAFR_Dimension_Tier
ON tbAFR_Dimension_Tier.Id = tbAFR_Dimension_SystemLocator.TierId
WHERE TierLevelId = 1010
GROUP BY [DomainName], [NetbiosName], tbAFR_Dimension_Tier.Name)
AS ResultTable
GROUP BY [DomainName], [NetbiosName], LastPolicy
ORDER BY [DomainName], [NetbiosName]

Save the results into a CSV or <whatever format> file as it suits you, and "DONE".

Who knows? Maybe this will end up as a native report in the Next Great Version???

Read the complete post at http://www.1e.com/techblog/post/2009/08/11/NightWatchman-Management-Centre-Query-to-Export-Machines-by-GROUP.aspx