SCCM report computers IP address Last logged user name

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.

13 Responses to "SCCM report computers IP address Last logged user name"

    1. 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
  1. 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
  2. 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
  3. 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
  4. 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 Reply to IlMuro Cancel reply