Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM report computers IP address Last logged user name

    SCCM report computers IP address Last logged user name

    Eswar KonetiBy Eswar KonetiJanuary 30, 3:34 pm1 Min Read SCCM 2007 8,720 Views
    Share
    Facebook Twitter LinkedIn Reddit

    long ago have posted report on computers with SCCM report for Last logged on computer name with application installed or not and SCCM report display computers with IPaddress based on Application/software

    this report gives you computers with ip address and last logged on username from a given collection :

    select A.Name0,c.IPAddress0 as 'IP ADDRESS',E.UserName0 as 'Lastlogged user'
    from v_R_System A inner join
    v_FullCollectionMembership B on
    A.ResourceID=B.ResourceID
    Inner join v_GS_NETWORK_ADAPTER_CONFIGUR  C ON
    A.ResourceID=C.ResourceID
    Inner join v_GS_COMPUTER_SYSTEM E ON A.ResourceID=E.ResourceID
    where CollectionID=@COLLID and C.IPEnabled0='1' and c.ipaddress0 !='0.0.0.0'
    group by A.Name0,c.IPAddress0 ,E.UserName0

    Prompt for collection:

    Select CollectionID,Name from v_Collection

     

    Note: Please replace the single quotes from your Key board as it might give you syntax error when you do copy/paste.

    Computers configmgr report IP Address last logged on user name sccm report SCCM report computers IP address Last logged user name
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    SCCM report list collections with no deployments

    December 05, 12:04 pm

    13 Comments

    1. shahid on November 4, 2015 8:29 PM

      hello ,

      Can we add email id to be displayed ? on this report

      Reply
      • Eswar Koneti on November 7, 2015 9:10 AM

        Yes you can but you need to add the custom attribute to AD user discovery method which pulls the information from Active directory but i guess emailID is already part of default discovery attribute .Just check from v_r_user .

        Reply
    2. IlMuro on October 5, 2012 5:00 PM

      Eswar doesn't specifically say it but you need to name the prompt COLLID.
      Thanks from me too.

      Reply
    3. Alex on July 18, 2012 9:09 PM

      Got the same must declare scalar variable for @COLLID
      Please provide the solution

      Thank you

      Reply
      • Eswar Koneti on July 18, 2012 9:26 PM

        did u create prompt for collID ? as given in post..

        Reply
    4. Brian on July 6, 2012 1:33 AM

      Anyway to also include model and serial number. trying to figure out how to attach it to this.
      SELECT sys.netbios_Name0 as [Netbios Name],
      BIOS.SerialNumber0 as [BIOS Serial Number],
      csys.Manufacturer0,
      csys.Model0 as [Model]
      FROM v_r_system as sys
      join v_GS_COMPUTER_SYSTEM as csys on sys.resourceid=csys.resourceid
      join v_gs_pc_bios as bios on sys.resourceid=bios.resourceid

      Reply
      • Eswar Koneti on July 6, 2012 8:23 AM

        this is Nice.

        Reply
    5. Brian on July 6, 2012 1:02 AM

      Nevermind, got it. helps when i name the prompt correctly.

      Reply
    6. Brian on July 6, 2012 12:56 AM

      I am getting the message must declare scalar variable for @COLLID as well.

      Reply
    7. Jake on June 26, 2012 11:57 AM

      Smacks own head! The collection I needed to run it on was a sub-collection, all works fine. Thanks for the query, appreciate it.

      Reply
      • Eswar Koneti on June 26, 2012 1:44 PM

        Glad you made it work !

        Reply
    8. Jake on June 26, 2012 11:24 AM

      Ah, I worked it out. However, when I run the report against the collection it shows "no matching records", even though there are 700 members.

      Reply
    9. Jake on June 26, 2012 7:50 AM

      Hi, I tried the above report but I get an error:
      Must declare the scalar variable @COLLID

      I'm new to SCCM/SQL so sorry if I missed something obvious.

      Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.