SCCM report for computers with last logged on user from collection

You have report to list all computers based on last logged on username from http://eskonr.com/2011/03/sccm-report-to-list-all-computers-based-on-last-logged-on-user-name-from-collection/ .

In this post,you will get list of computers with last logged on user name from given collection.

Create a new report and paste the below query into it.

Select A.Name0, A.UserName0  from V_GS_COMPUTER_SYSTEM A join v_FullCollectionMembership B on A.ResourceID = B.ResourceID Where B.CollectionID =@collID

Create a prompt for collection ID collID :

select  CollectionID,Name from v_Collection

Please refer the post why should u use V_GS_COMPUTER_SYSTEM for user name rather V_R_SYSTEM http://smsug.ca/blogs/garth_jones/archive/2007/06/30/482.aspx

15 Responses to "SCCM report for computers with last logged on user from collection"

  1. Hi Eswar,
    Can you please assist.

    Need a listing of all workstations in SCCM 2012, Last User that logged in, Model of the device, OS of the device and region.

    I'm trying to learn SQL as will be getting a lot of these request, how do I start.

    Reply
  2. I would like to get a list of all our computers and across each computer I would like to know all users that have been signed on to that computer over the past 30 days

    Reply
    1. You can get this report ,all you need to do is, identify the attribute value for 30 days that store this information and use where like condition.
      I believe you can use computer lastlogon timestamp which may not be the accurate but it comes from active directory .

      Regards,
      Eswar

      Reply
    1. Hi Sooraj,
      Details do exist in v_r_system and v_r_user ,you can join these 2 tables to get user information.

      Regards,
      Eswar

      Reply
  3. I have a list of computers which are not in the same collection and I need to know what usernames are for this list of computers. Would it be a power-shell Script? could you help me with this?

    Reply
    1. Hi Rad,
      You can get the usernames using powershell script but i dont have script readily available. The easiest way for now would be ,add the list of clients into collection and use the report to find username. I will update you with powershell script when i get time.

      Regards,
      Eswar

      Reply
  4. Hi Eswar - just found your blog... and quickly subscribed!! 🙂
    I realize this is years after the original post, but I am hoping you can still help.
    My requirement is to produce a CSV-report listing computer name, last logged-on user and all applications installed on the computer.
    I will then query AD using PowerShell to produce a listing of logonID, full username and department.
    That way, I can break down by department the users, computers and applications (to be used for a computer-refresh)
    Many thanks for whatever help/guidance you can provide!
    I'm decent with PowerShell, SQL, etc., but SCCM is new to me.

    Reply
    1. Hi Mark,
      Apologies for the delay ,i know this is almost like 4 months . Have you looked at the default reports for the applications installed on each PC?
      you can get the user name info from v_r_system or V_r_system_valid which you can then export into CSV or any other format you like from SSRS Report.

      Reply
  5. Can the report be modified to specific workstation? So instead of prompting for a collection, it prompts for a specific workstation? If so, how would you modify the report?

    Thanks!

    Reply
    1. that will be one line query: Select A.Name0, A.UserName0 from V_GS_COMPUTER_SYSTEM A where a.Name0=@woorkstation
      prompt for workstation: select name0 from V_GS_COMPUTER_SYSTEM order by name0

      Reply

Post Comment