Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

List all assets associated wtih a username/user ID

Created: 17 Oct 2012 | 3 comments

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?

Comments 3 CommentsJump to latest comment

Ludovic Ferre's picture

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) 

I am currently off-net, on a retreat of some kind. I'll be back real soon, and you sure will hear from me then ;-).

Ludovic FERRÉ
Principal Remote Product Specialist
Symantec

Sachin Sawant's picture

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

Sachin Sawant's picture

Dear Bill.vannort please reply if your issue is resolved