I'm looking to add a column to several of the canned reports that displays the domain user name or current console user associated with a computer name when running an Asset Intelligence report for specific software installs.

As an example, I can run the Software 06a report to search for all instances of a specific version of Adobe Acrobat.  I can then drill into the report (which opens the Software -02D report)  to get the specific computers that have the software installed on them.  I'm wish to make this report a bit more descriptive and also provide me with the username associated with that computer account within that same report.

Thanks,

George

Clone the report named: Software 02D - Computers with a specific software product

And then use below query to get the result you are looking for--

***************************************

if(@SoftwareID='')
 begin
 select @SoftwareID=NULL
  end
  if (@SoftwarePropertiesHash = '')
  begin
  select @SoftwarePropertiesHash = NULL
  end
  if(@CollectionID = '')
  begin
  select @CollectionID = NULL
  end
  SELECT DISTINCT
  MEMB.Netbios_Name0 AS [Computer Name],
  [Top Console User] = CASE
 when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
 then 'Unknown'
 Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
 End, 
  v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Computer Manufacturer],
  v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model],
  v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductName0 AS [Product Name],
 v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 AS [Product ID],
 v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CM_DSLID0 as [DSL ID],
 v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallType0 as [Installation Type]
 FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
  INNER JOIN
  (
   select v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 from v_R_System_Valid
 INNER JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID
 where v_FullCollectionMembership.CollectionID = @CollectionID
  )MEMB on MEMB.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
  INNER JOIN v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = MEMB.ResourceID
  Inner join v_R_System_Valid on v_R_System_Valid.ResourceID = MEMB.ResourceID
  left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
  where
  (@SoftwareID IS NOT NULL OR @SoftwarePropertiesHash IS NOT NULL) AND
  (@SoftwareID IS NULL OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID  = @SoftwareID) AND
  (@SoftwarePropertiesHash IS NULL OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0 = @SoftwarePropertiesHash) and
   (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_R_System_Valid.ResourceID)

***************************************

Thanks!

Thank you Vinayak, that provided exactly what I needed.

Out of curiosity, would you happen to know why a good portion of the entries for the Top Console user field would be listed as unknown?

-- George

Have you enabled the Audit policy? It is needed for Top console user.

http://technet.microsoft.com/en-ca/library/cc431373.aspx

Garth --

I do have this policy enabled for both successes and failures via AD group policy.  Not all entries are listed as unknown, but a good portion, about 25% are.

George

Are they shared desktops? The TCU is defined as the user with 60% logon time, until that happens the TCU will be unknown.

Garth -

The systems are single use, logged in only by the assigned user via AD account.  I might add that we just recently deployed the configmgr agents to the entire user base about two months ago, so I'm guessing there could be some delay there.  Are there any client logs that reference this information?

Thanks,

George

This issue might be the size of your event view logs

Yes you can look at the asset advisor log, this should tell you if there is an issue.

You can also use wbemtest (as Local system) and manually query WMI to see what is stored with WMI too. the query you need is within the log asset advisor log.

Sorry this is super old. I have SCCM 2012 R2 and I'm not sure where/how to add the above into the cloned Software 02D report. Help?