Client Management Suite

Does anyone have insight on how to build a query in Altiris 7.1 to display all assets assigned to a username or user ID?

Hello Bill,

Are you interested in all asset types or specific assets?

I would suggest you start from the database with the user name or user id.

If you can craft a SQL query yourself, you are best starting in the Management Studio and looking around in the database to find the tables or view that contains the information you need.

Here are some key queries I use whenever I need to complete a task like this:

Get SMP data classes that may contain user information:

select name, datatablename
  from dataclass
 where name like '%user%' 

Get a list of tables that have a certain column:

select so.name as 'Table', sc.name as 'Column'
  from sys.objects so
  join sys.columns sc
    on so.object_id = sc.object_id
 where sc.name like '%user%' 
 order by so.name, sc.name

 

With the result you can then try to map the different tables, with the following keys (again) to link them together:

• In resource tables the uniqueidentifier for a resource will be "ResourceGuid".
• For dataclass (and reporting facing view) the resourceguid is prepended with an underscore and thus is "_ResourceGuid". This is used to hide the guids from report (where the fields starting with underscore are not shown)
• Guid is used for internal tables when the type does not matter (because the item is saved at a lower level than resource and is not user facing) 

 

try this, SELECT distinct dbo.Inv_AeX_AC_Identification.[Name] ,dbo.Inv_UG_User_Account.[User Name],[Full Name] FROM dbo.Inv_AeX_AC_Identification, dbo.Inv_UG_User_Account WHERE dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_UG_User_Account._ResourceGuid

Dear Bill.vannort please reply if your issue is resolved