none
Need SCCM 2007 Custom Report for All Computers with All last logged in Users over a 30 day period? RRS feed

  • Question

  • Does anyone have a starting point in the SCCM built in reports or a report that will list all my clients and their last logged in user over a 30 day period?  Help I need this report ASAP.

    Thank you Gary Pearson


    Monday, September 10, 2012 7:44 PM

Answers

All replies

  • So let me try an understand what you are looking for..

    You want a report of users, and you want to know when they last logon to the domain or PC?

    Keep in mind that CM07 is not really the best place to get this data, this is more of an AD query.


    http://www.enhansoft.com/

    Monday, September 10, 2012 10:02 PM
    Moderator
  • I am looking for a report that shows all the Users that have logged into all the PC's over a 30 day periord.  So some PC's will have multiple users that have logged into it.  I was thinking of using last logged in user on the PCs.  Not sure where to start especially since the report will be limited to the last 30 days.

    Thank you for responding and I am under a deadline.  Please help.


    gary pearson

    Tuesday, September 11, 2012 11:32 AM
  • Do you have access to a powershell or vbs script that would access the last logged on users to a workstation?   Or is there a report you have code for that will at least give the logged on users based on last hardware scan?

    Please get back,


    gary pearson

    Monday, September 17, 2012 3:23 PM
  • did you tried this ? http://eskonr.com/2011/02/sccm-report-for-computers-where-no-user-logged-in-for-past-20-days/

    change the number of days to 30 and should be <(less than) ..that should do the trick.


    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Tuesday, September 18, 2012 2:55 PM
  • Thank you Eswar for the response.

    I do have this code in my report,  but the report I have come up with is inacurate.  It will not list All the Computers the user name has logged into in the last 30days.  It only list the most recent ones based on hardware inventory which is set for once per day:  Here is the code can you help?

    SELECT  CS.UserName0, CS.Domain0, SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0, BIOS.Manufacturer0, BIOS.SerialNumber0, CS.Model0, SYS.Operating_System_Name_and0, WS.LastHWScan
    FROM v_R_System SYS
    JOIN v_GS_PC_BIOS Bios on  sys.ResourceID =  BIOS.ResourceID
    JOIN v_GS_COMPUTER_SYSTEM CS on sys.ResourceID = CS.ResourceID
    INNER JOIN v_GS_WORKSTATION_STATUS WS on  CS.ResourceID = WS.ResourceID
    WHERE Datediff(dd,WS.LastHWScan,getdate()) > -1 AND Datediff(dd,WS.LastHWScan,getdate()) < 31

    ORDER BY SYS.Netbios_Name0


    gary pearson

    Tuesday, September 18, 2012 3:20 PM
  • Tuesday, September 18, 2012 7:46 PM
    Moderator
  • This query will give the computers details. But is it possible to get the user accounts details that has not logged in last 30 days. 
    Sunday, February 16, 2014 11:20 AM
  • This query will give the computers details. But is it possible to get the user accounts details that has not logged in last 30 days. 

    You are better off using AD for this information.

    http://www.enhansoft.com/

    Sunday, February 16, 2014 6:15 PM
    Moderator
  • Thanks this worked pretty good. Here is a slight modification.

    It will give you the 2x versions of the User name (Domain\User & User), Full User Name, Computer Name, Serial Number, Model, OS, and date collected. - I dropped the extra info I did not need.

    SELECT  CS.UserName0, US.User_Name0, US.Full_User_Name0, SYS.Netbios_Name0, BIOS.SerialNumber0, CS.Model0, SYS.Operating_System_Name_and0, WS.LastHWScan
     FROM v_R_System SYS
     JOIN v_GS_PC_BIOS Bios on  sys.ResourceID =  BIOS.ResourceID
     JOIN v_GS_COMPUTER_SYSTEM CS on sys.ResourceID = CS.ResourceID
     LEFT JOIN v_R_User US on CS.UserName0 = US.Unique_User_Name0
     INNER JOIN v_GS_WORKSTATION_STATUS WS on  CS.ResourceID = WS.ResourceID
     WHERE Datediff(dd,WS.LastHWScan,getdate()) > -1 AND Datediff(dd,WS.LastHWScan,getdate()) < 31
    ORDER BY SYS.Netbios_Name0

    Just in case someone is looking for similar. - I needed to cross reference a list of computers with Users, but needed the Full Name and the logon User Name.

    Cheers!


    Virtual Samurai - MCP, CCNA

    Tuesday, April 1, 2014 2:46 PM
  • But that doesn't give you all the users that logged in.  For that use V_GS_System_Console_User.

    Select cs.name0, u.systemconsoleUser0

    from v_gs_computer_system cs

    inner join v_gs_system_console_user u on u.resourceID = cs.resourceID

    where u.lastconsoleUse0 > getdate() -30

    order by cs.name0, u.systemconsoleUser0

    
    Tuesday, April 1, 2014 3:00 PM