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 Comments
hello ,
Can we add email id to be displayed ? on this report
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 .
Eswar doesn't specifically say it but you need to name the prompt COLLID.
Thanks from me too.
Got the same must declare scalar variable for @COLLID
Please provide the solution
Thank you
did u create prompt for collID ? as given in post..
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
this is Nice.
Nevermind, got it. helps when i name the prompt correctly.
I am getting the message must declare scalar variable for @COLLID as well.
Smacks own head! The collection I needed to run it on was a sub-collection, all works fine. Thanks for the query, appreciate it.
Glad you made it work !
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.
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.