none
SCCM Report to prompt for computer name and return the last Logged on person with time stamp.

    Question

  • I tried to search this forum for a SCCM Report to prompt for computer name and return the last Logged on person with time stamp, but didn't find it.  I'm wondering if the time stamp part could work in our environment being that asset intelligence has not been enabled yet.  If some has this report and could write it out, I would be so greatly appreciated.

    Sincerely,

     

    Mark 

    Tuesday, December 14, 2010 5:17 PM

Answers

  • try adding in this Where clause:

     

    AND v_GS_SYSTEM_CONSOLE_USER.Point = 1

    where v_r_system.netbios_name0 = @ComputerName

    Order by v_R_System.Netbios_Name0

    fyi, slightly related but sort of off topic:  console usage is reported by hardware inventory; so keep that in mind for how up to date the data is.  Additionally, if you are looking (or management is looking) to use this report as a basis to say "Bob Jones didn't log in until 8:15am, he was supposed to be working by 8:00am", ConfigMgr is not the right place for policing your employees.  It's simply not meant to do that; and in fact, that employee could sue (and win) if the company used this data for any disciplinary action the company took.  So... if that's what this was meant for; make sure management knows they can't use it.


    Standardize. Simplify. Automate.
    Tuesday, December 14, 2010 9:09 PM
  • We turned off Classic reporting... we only use SRS these days.  So um... from memory.  yeah, there's a prompt area?  and you basically have to name a prompt the same as the prompt you defined.  so put in a prompt name of  "computername"

    Basically, just look at any other classic report which currently says in the title "by computer"; to see how a prompt by computer name is designed.


    Standardize. Simplify. Automate.
    Tuesday, December 14, 2010 10:08 PM

All replies

  • I have a report (SHOWN BELOW) that shows the last logged on user for all of computers in my organization with time stamp, but need my report to prompt for the computer name and display the last logged on user with time stamp.  Yes, the time stamp does work in my organization.

    Select distinct
    v_R_System.Netbios_Name0 AS "Computer Name",
    v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
    v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
    v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
    v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
    from v_R_System INNER JOIN
    (SELECT
    ResourceID,
    SystemConsoleUser0,
    NumberOfConsoleLogons0,
    TotalUserConsoleMinutes0,
    LastConsoleUse0,
    ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
    FROM
    v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
    ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
    AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
    Order by v_R_System.Netbios_Name0

     

    Tuesday, December 14, 2010 6:05 PM
  • try adding in this Where clause:

     

    AND v_GS_SYSTEM_CONSOLE_USER.Point = 1

    where v_r_system.netbios_name0 = @ComputerName

    Order by v_R_System.Netbios_Name0

    fyi, slightly related but sort of off topic:  console usage is reported by hardware inventory; so keep that in mind for how up to date the data is.  Additionally, if you are looking (or management is looking) to use this report as a basis to say "Bob Jones didn't log in until 8:15am, he was supposed to be working by 8:00am", ConfigMgr is not the right place for policing your employees.  It's simply not meant to do that; and in fact, that employee could sue (and win) if the company used this data for any disciplinary action the company took.  So... if that's what this was meant for; make sure management knows they can't use it.


    Standardize. Simplify. Automate.
    Tuesday, December 14, 2010 9:09 PM
  • I did this and am receiving:

    ---------------------------
    Report SQL Statement
    ---------------------------
    [42000][137][Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@ComputerName".
    ---------------------------
    OK  
    ---------------------------

    Select distinct
    v_R_System.Netbios_Name0 AS "Computer Name",
    v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
    v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
    v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
    v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
    from v_R_System INNER JOIN
    (SELECT
    ResourceID,
    SystemConsoleUser0,
    NumberOfConsoleLogons0,
    TotalUserConsoleMinutes0,
    LastConsoleUse0,
    ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
    FROM
    v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
    ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
    AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
    where v_r_system.netbios_name0 = @ComputerName
    Order by v_R_System.Netbios_Name0

    Tuesday, December 14, 2010 9:24 PM
  • yes, that's a prompted report.  are you putting this ONLY in SQL Management Studio?  The prompt in there of @ComputerName would be something you need to define, either when you build the classec SMS report in the classic SMS reporting wizard; or when you use Report Builder 2.0 (or 3.0) against your already-working-and configured ConfigMgr SRS reporting site.

     


    Standardize. Simplify. Automate.
    Tuesday, December 14, 2010 9:29 PM
  • I'm sorry, I'm new to SCCM Reports.  Is that done in the Prompts area?  what would it look like, please.

     

    Mark

    Tuesday, December 14, 2010 9:34 PM
  • We turned off Classic reporting... we only use SRS these days.  So um... from memory.  yeah, there's a prompt area?  and you basically have to name a prompt the same as the prompt you defined.  so put in a prompt name of  "computername"

    Basically, just look at any other classic report which currently says in the title "by computer"; to see how a prompt by computer name is designed.


    Standardize. Simplify. Automate.
    Tuesday, December 14, 2010 10:08 PM
  • We turned off Classic reporting... we only use SRS these days.  So um... from memory.  yeah, there's a prompt area?  and you basically have to name a prompt the same as the prompt you defined.  so put in a prompt name of  "computername"

    Basically, just look at any other classic report which currently says in the title "by computer"; to see how a prompt by computer name is designed.


    Standardize. Simplify. Automate.


    We can ue following SQL query in SQL statement:-

    Select
    V_GS_SYSTEM.Name0 as [ComputerName],
    V_GS_NETWORK_LOGIN_PROFILE.TimeStamp as [Last Login Time],
    V_GS_NETWORK_LOGIN_PROFILE.Name0 as [Logon User],
    V_GS_SYSTEM.Domain0 as [Logon Domain],
    V_GS_SYSTEM.SystemRole0 as [System Role],
    V_GS_SYSTEM.SystemType0 as [System Type]
    from V_GS_NETWORK_LOGIN_PROFILE
    left JOIN v_GS_SYSTEM ON V_GS_NETWORK_LOGIN_PROFILE.ResourceID= v_GS_SYSTEM.ResourceID
    where V_GS_SYSTEM.Name0 = @ComputerName
    Order by V_GS_SYSTEM.Name0

    Also in in prompt provide following information:-

    Name:- Computername

    Prompt Text:- Computername

    Select Provide a SQL statement and put following SQL statement:-

     

    begin
     if (@__filterwildcard = '')
      SELECT DISTINCT V_GS_SYSTEM.Name0 from V_GS_SYSTEM  ORDER By V_GS_SYSTEM.Name0
     else
      SELECT DISTINCT V_GS_SYSTEM.Name0 from V_GS_SYSTEM
      WHERE V_GS_SYSTEM.Name0 like @__filterwildcard
      ORDER By V_GS_SYSTEM.Name0
    end

     I have tested successfully and it is working fine.

    Thanks

    Nilesh Patil

    MCTS/MCITP/CCNA/OCA/ITIL

     

     

     


    Nilesh Patil, MCSE,MCTS,MCITP,CCNA,OCA,ITIL
    • Edited by Nilesh Patil Thursday, December 22, 2011 10:53 AM
    Thursday, December 22, 2011 10:52 AM
  • try this http://eskonr.com/2011/03/sccm-report-for-computers-with-last-logged-on-user-from-collection/ could be of simple to get last logged on user name based on collection given.


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

    Eswar Koneti | My Tech blog: eskonr.com | Linkedin: Eswar Koneti
    Thursday, December 22, 2011 1:23 PM
  • Nilesh, FYI only, unfortunately there are two problems with this query..

    1.      V_GS_NETWORK_LOGIN_PROFILE is not a default view within CM07

    2.      TimeStamp is an internal CM07 column and it does not mean that last time the person logon. It mean this is when the data was added to the CM07 db.


    http://www.enhansoft.com/
    Thursday, December 22, 2011 11:51 PM
    Moderator
  • I have tried all steps you mentioned but encounter with below error. Please help me to get a user name after entering a computer name in the repot

    Report Name:

    Computer Name to Users

    Category:

    Custom Reports

    Comment:

    Parameters:

    Computername

    Report.asp?ReportID=47

    Top of Form 1

    An error occurred when the report was run. The details are as follows:

    Invalid object name 'V_GS_NETWORK_LOGIN_PROFILE'.

    Error Number:

    -2147217865

    Source:

    Microsoft OLE DB Provider for SQL Server

    Native Error:

    208

    Monday, May 27, 2013 3:42 PM
  • Please help me to get a user name after entering a computer name in the repot

    Monday, May 27, 2013 3:42 PM
  • I have tried all steps you mentioned but encounter with below error. Please help me to get a user name after entering a computer name in the repot

    Report Name:

    Computer Name to Users

    Category:

    Custom Reports

    Comment:

    Parameters:

    Computername

    Report.asp?ReportID=47

    Top of Form 1

    An error occurred when the report was run. The details are as follows:

    Invalid object name 'V_GS_NETWORK_LOGIN_PROFILE'.

    Error Number:

    -2147217865

    Source:

    Microsoft OLE DB Provider for SQL Server

    Native Error:

    208

    <input id="2a09293f-2090-490f-ac54-b6b9e864ccb8_attachments" type="hidden" />
    Tuesday, May 28, 2013 4:03 PM
  • Hi,

     Please let me know how to generate last login time with hostname based on the collection. 

    In above query we can generate based on the particular machine.

    Regards,

    Abdul


    • Edited by kaoo313 Monday, August 26, 2013 7:47 AM
    Monday, August 26, 2013 7:34 AM
  • Please let me know how to generate last login time with hostname based on the collection. 

    In above query we can generate based on the particular machine.


    What is unclear about this post? We can help you but we must understand, what is unclear.

    http://www.enhansoft.com/

    Monday, August 26, 2013 11:48 AM
    Moderator
  • Hi,

    I want a sql query

    to prompt for computer name and return the last Logged time based on collection


    Monday, August 26, 2013 12:21 PM
  • I want a sql query

    to prompt for computer name and return the last Logged time based on collection



    So what is wrong with the query above?

    http://www.enhansoft.com/

    Monday, August 26, 2013 12:30 PM
    Moderator
  • that query generating the report from all the users in the environment.

    I want to generate the report based on per collection. In our enviornment we have different collection based on location. so i would like to generate the report hostname with last logged time by user in each collection. Please adivse me to get it 


    • Edited by kaoo313 Monday, August 26, 2013 3:41 PM
    Monday, August 26, 2013 12:34 PM
  • Hi Garth,

    Any update please

    Tuesday, August 27, 2013 4:21 AM
  • Again, what doesn't work, when you use the link above from Eswar Koneti... the one which says "by collection" in it?

    Standardize. Simplify. Automate.

    Tuesday, August 27, 2013 9:13 PM
  • HI,

    See if this helps?

    select VRS.Name0 as 'Computer Name'
    , Resource_Domain_OR_Workgr0
    , User_Name0 'Last Logon Name'
    , VOS.Caption0
    ,VOS.CSDVersion0

    from v_R_System VRS 
    left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID

    where Client0 = 1 and lower(VRS.name0) = lower(@machinename)


    Http://blog.theinfraguys.com | Please remember to click “Mark as Answer” on my answer if it helps. :)

    Tuesday, August 27, 2013 11:38 PM
  • Hi Vicez,

    I am  not lookin Last Logon Name. I am looking for last login time based on collection

    Wednesday, August 28, 2013 4:08 AM
  • Hi Sherry,

    If i am running the script provided by Eswar, that will generate the last logon user name. I am looking for last login time with hostname and user name

    Wednesday, August 28, 2013 4:57 AM
  • Hi,

    Use this

    select VRS.Name0 as 'Computer Name'
    , Resource_Domain_OR_Workgr0
    , User_Name0 'Last Logon Name'
    , VOS.Caption0
    ,VOS.CSDVersion0
    ,VSC.LastConsoleUse0

    from v_R_System VRS 
    left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID
    left join dbo.v_GS_SYSTEM_CONSOLE_USER VSC on VSC.SystemConsoleUser0 = VRS.User_Name0


    Http://blog.theinfraguys.com | Please remember to click “Mark as Answer” on my answer if it helps. :)

    Wednesday, August 28, 2013 5:11 AM
  • Hi Vinez,

    Above query will generate the report of hostname and last logon user name. I want to add one more colum last login time also

    Wednesday, August 28, 2013 5:59 AM
  • I have already added in additional queries as per the text in italic and underlined

    select VRS.Name0 as 'Computer Name'
    , Resource_Domain_OR_Workgr0
    , User_Name0 'Last Logon Name'
    , VOS.Caption0
    ,VOS.CSDVersion0
    ,VSC.LastConsoleUse0

    from v_R_System VRS 
    left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID
    left join dbo.v_GS_SYSTEM_CONSOLE_USER VSC on VSC.SystemConsoleUser0 = VRS.User_Name0


    Http://blog.theinfraguys.com | Please remember to click “Mark as Answer” on my answer if it helps. :)

    Wednesday, August 28, 2013 6:09 AM
  • Hi Vinez,

    While running this query, Last console use is getting NULL value

    Wednesday, August 28, 2013 7:21 AM
  • please use this instead

    select VRS.Name0 as 'Computer Name'
    , Resource_Domain_OR_Workgr0
    , User_Name0 'Last Logon Name'
    , VOS.Caption0
    ,VOS.CSDVersion0
    ,VSC.LastConsoleUse0
    ,VSC.NumberOfConsoleLogons0 'Total Console Logons Counts'
    ,VSC.TotalUserConsoleMinutes0 'TOtal COnsole Logon Minutes'

    from v_R_System VRS 
    left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID
    left join dbo.v_GS_SYSTEM_CONSOLE_USER VSC on VSC.ResourceID = VRS.ResourceID


    Http://blog.theinfraguys.com | Please remember to click “Mark as Answer” on my answer if it helps. :)

    • Proposed as answer by Vincez Wednesday, August 28, 2013 8:34 AM
    Wednesday, August 28, 2013 8:00 AM
  • Hi Vncez

    While running this query , its generating 3880 machines report. but in our enviornment we are having 1330 machines. 

    Also it is better to generate the report based on collection id. instead of getting all login time details from entire enviornmet. any hopes


    • Edited by kaoo313 Wednesday, August 28, 2013 11:10 AM
    Wednesday, August 28, 2013 8:31 AM
  • Remember that ANY CM query will NOT give your actuate results with respect to last logon times or last logon user. you need to query AD for that. Any that CM provide will be a best guess answer.

    http://www.enhansoft.com/

    Wednesday, August 28, 2013 11:15 AM
    Moderator
  • Thanks Garth,

    could you help to get the batch file for uninstalling utorrent software 

    Thursday, August 29, 2013 10:23 AM
  • Thanks Garth,

    could you help to get the batch file for uninstalling utorrent software 


    I don't have one, I sure that with a quick Bing search you will be able to find one.

    http://www.enhansoft.com/

    Thursday, August 29, 2013 10:47 AM
    Moderator
  • Any one can help to get the batch file for uninstalling utorrent software 
    Thursday, August 29, 2013 12:15 PM
  • Hello All,

    Can I get the batch file for uninstalling utorrent software

    Friday, September 06, 2013 5:17 AM
  • Hello All,

    Can I get the batch file for uninstalling utorrent software


    Please create your own thread in the correct forum.

    http://www.enhansoft.com/

    Friday, September 06, 2013 10:48 AM
    Moderator