Home » Groups » Asset & CMDB

Easily Add a Primary User Column to Your Reports

Easily Add a Primary User Column to Your Reports
Filed under: Inventory Solution, Asset Management Solution Inventory
robertser's picture
Submitted by robertser on 9 September, 2008 - 08:39

It is nice to be able to see who the primary user is in a report. Since the Primary User table is based on a given month you have to add that as a parameter to every report you want to see Primary User on.

With this SQL view you can easily join it to your custom reports and always have the Primary User for the current month without having to create parameters.

Copy the below code into a new view in your Altiris database. Name it something easy like vw_PC_to_PrimaryUser. 

SELECT DISTINCT _ResourceGuid AS 'Guid', Month, [User], Domain
FROM     dbo.Inv_AeX_AC_Primary_User
WHERE   (Month = DATENAME(Month, GETDATE())) AND ([User] <> '')
UNION
SELECT DISTINCT _ResourceGuid AS 'Guid', Month, [User], Domain
FROM     dbo.Inv_AeX_AC_Primary_User AS Inv_AeX_AC_Primary_User_2
WHERE   (Month = DATENAME(Month, DATEADD(Month, - 1, GETDATE()))) AND (_ResourceGuid NOT IN
             (SELECT   _ResourceGuid
              FROM     dbo.Inv_AeX_AC_Primary_User AS Inv_AeX_AC_Primary_User_1
              WHERE   (Month = DATENAME(Month, GETDATE())) AND ([User] <> '')))

Here is an example of SQL code to use the view. 

select name, u.[User]
from vComputer vc
join vw_PC_to_User u on vc.guid=u.guid
4.13793
Average: 4.1 (29 votes)

vcomputer view

mgreen's picture
Submitted by mgreen on 1 December, 2008 - 16:51.

Keep in mind that the [user] column in the vcomputer view is already the primary user.

Here is a good query to get the primary user for the current month for all assets:

select i.[name], [user]
from inv_aex_ac_primary_user pu
join item i on i.guid = pu._resourceguid
where SUBSTRING([month],1,3) = SUBSTRING(CONVERT(nvarchar(3), GETDATE()),1,3)