Building Reports to Include the Most Logged on User in the Altiris Database

I've noticed when using the Altiris Report Builder and adding user accounts to a report I sometimes end up with incorrect information. The reason is the Report Builder allows you to make a link between tables where information is not unique. This introduces some randomness into the relationship between the two tables and can result in unexpected results.

For Example:

Check out the results I get when I run this query and view the results using a query analyzer:

SELECT PU.*, PC.[Name]
FROM Inv_AeX_AC_Primary_User PU
INNER JOIN vComputer PC ON PC.[Guid]=PU.[_ResourceGUID]
WHERE PC.[Name]= 'pc_names_goes_here'

Click to view.

You can see that several user accounts are considered as primary accounts.

In a simple query based on ResourceGuid matching, any name might be given. This might not be the most logged on user. This information is often needed in reports to be able to contact the user.

How can we be sure to match the absolute most logged on user on a machine?

Solution:

First, let's have a look on how many users are counted and their score:

SELECT PC.[Name],PU.[User],COUNT(PU.[User])
FROM Inv_AeX_AC_Primary_User PU
INNER JOIN vComputer PC ON PC.[Guid]=PU.[_ResourceGUID]
WHERE PC.[Name]= 'pc_names_goes_here'
GROUP BY PC.[Name],PU.[User]
ORDER BY PC.[Name],COUNT(PU.[User])DESC

Click to view.

Next, as this result is different for every managed computer in the database, we need to achieve this highest number. The trick is this: order descending and only take first line:

SELECT TOP 1(COUNT(PU.[User]))
FROM Inv_AeX_AC_Primary_User PU
INNER JOIN vComputer PC ON PC.[Guid]=PU.[_ResourceGUID]
WHERE PC.[Name]= 'pc_names_goes_here'
GROUP BY PC.[Name],PU.[User]
ORDER BY COUNT(PU.[User])DESC

Click to view.

So, use this query as a sub-query to match a computer and the user account which is most counted.

SELECT PC.[Name],PU.[User]
FROM Inv_AeX_AC_Primary_User PU
INNER JOIN vComputer PC ON PC.[Guid]=PU.[_ResourceGUID]
WHERE PC.[Name]= 'pc_names_goes_here'
GROUP BY PC.[Name],PU.[User]
HAVING COUNT(PU.[User])= (
	SELECT TOP 1(COUNT(PU.[User]))
	FROM Inv_AeX_AC_Primary_User PU
	INNER JOIN vComputer PC 
ON PC.[Guid]=PU.[_ResourceGUID]
	WHERE PC.[Name]= 'pc_names_goes_here'
	GROUP BY PC.[Name],PU.[User]
	ORDER BY COUNT(PU.[User])DESC)

Click to view.