Inventory report for laptops who have webcam feature ?
You can use this report both on SCCM 2007 and Configmgr 2012 Environment.
select cs.name0 [Computer name],
cs.username0 [Last loggedin User],
os.Caption0 [OS],os.InstallDate0 [OS Installed Date],
cs.Manufacturer0,cs.Model0 ,
WS.LastHWScan [Reported Date to SCCM]
from v_GS_COMPUTER_SYSTEM cs,v_GS_SYSTEM_ENCLOSURE se,v_GS_OPERATING_SYSTEM OS, v_GS_WORKSTATION_STATUS WS
where cs.ResourceID=se.ResourceID and
cs.ResourceID=OS.ResourceID and
cs.ResourceID=WS.ResourceID and
se.ResourceID in (select ResourceID from v_GS_SYSTEM_DEVICES where Name0 like '%camera%' or Name0 like '%webcam%')
and SE.ChassisTypes0 in ( '8','9','10','11','12','14','18','21')
group by cs.Name0,cs.UserName0,cs.Manufacturer0,cs.Model0,OS.Caption0,OS.InstallDate0,WS.LastHWScan
Note: There reports are created for for Dell and Lenovo models only.Webcam name differs from manufacture to manufacture,so if you have other models like hp,ThinkPad etc,you should look at resource explorer—>hardware –>system devices for the webcam name.
Who do not have: use Not in syntax:
select cs.name0 [Computer name],
cs.username0 [Last loggedin User],
os.Caption0 [OS],os.InstallDate0 [OS Installed Date],
cs.Manufacturer0,cs.Model0 ,
WS.LastHWScan [Reported Date to SCCM]
from v_GS_COMPUTER_SYSTEM cs,v_GS_SYSTEM_ENCLOSURE se,v_GS_OPERATING_SYSTEM OS, v_GS_WORKSTATION_STATUS WS
where cs.ResourceID=se.ResourceID and
cs.ResourceID=OS.ResourceID and
cs.ResourceID=WS.ResourceID and
se.ResourceID not in (select ResourceID from v_GS_SYSTEM_DEVICES where Name0 like '%camera%' or Name0 like '%webcam%')
and SE.ChassisTypes0 in ( '8','9','10','11','12','14','18','21')
group by cs.Name0,cs.UserName0,cs.Manufacturer0,cs.Model0,OS.Caption0,OS.InstallDate0,WS.LastHWScan
SSRS Report:
To create collection,follow the blog post how to create http://eskonr.com/2010/11/sccm-collection-to-list-all-the-laptop-computers-2/
10 Comments
Thx i have use your request to found all Jabra device with the name of this device ! 🙂
select cs.name0 [Computer name],
cs.username0 [Last loggedin User],
os.Caption0 [OS],os.InstallDate0 [OS Installed Date],
cs.Manufacturer0,cs.Model0 ,
WS.LastHWScan [Reported Date to SCCM],
GD.Name0 [Jabra]
from v_GS_COMPUTER_SYSTEM cs,v_GS_SYSTEM_ENCLOSURE se,v_GS_OPERATING_SYSTEM OS, v_GS_WORKSTATION_STATUS WS, v_GS_SYSTEM_DEVICES GD
where cs.ResourceID=se.ResourceID and
cs.ResourceID=OS.ResourceID and
cs.ResourceID=WS.ResourceID and
se.ResourceID=GD.ResourceID
and GD.Name0 LIKE N'%Jabra%'
group by cs.Name0,cs.UserName0,cs.Manufacturer0,cs.Model0,OS.Caption0,OS.InstallDate0,WS.LastHWScan,GD.Name0
Thanks Quentin.This query will help others.
Regards,
Hello - is there a way I can run this against a specific collection? and not all my systems? please help
Hi,
You can use v_fullcolleectionmemership as inner join and specify collection Id
I'm not really sure what that means or where to add it to your query language
Try this to get webcam devices against specific collection.
select cs.name0 [Computer name],
cs.username0 [Last loggedin User],
os.Caption0 [OS],os.InstallDate0 [OS Installed Date],
cs.Manufacturer0,cs.Model0 ,
WS.LastHWScan [Reported Date to SCCM]
from v_GS_COMPUTER_SYSTEM cs,v_GS_SYSTEM_ENCLOSURE se,v_GS_OPERATING_SYSTEM OS, v_GS_WORKSTATION_STATUS WS,v_FullCollectionMembership fcm
where cs.ResourceID=se.ResourceID and
cs.ResourceID=OS.ResourceID and
cs.ResourceID=WS.ResourceID and
cs.ResourceID=fcm.ResourceID and
se.ResourceID in (select ResourceID from v_GS_SYSTEM_DEVICES where Name0 like '%camera%' or Name0 like '%webcam%')
and SE.ChassisTypes0 in ( '8','9','10','11','12','14','18','21')
and fcm.CollectionID='PS100034'
group by cs.Name0,cs.UserName0,cs.Manufacturer0,cs.Model0,OS.Caption0,OS.InstallDate0,WS.LastHWScan
Thanks,
Eswar
Please add a new field to show the status of the camera (Enabled/Disable)
Hi Ayman,
I do not have devices with webcam to test it but if the device has webcam installed, it must be enabled and ready to use. What do you mean enable or disable ?
Regards,
Eswar
Dear Eswar,
i Means the status of camera (Enabled/Disable) because we have Disable almost Camera in Laptop
Because we in Environment of Domain and company policy rules is disable Camera in Laptop
But needs to make sure from this disable it by Report
notes Disable done on the drive from Dives Manger --> Right click on driver then Disbale
To bring whether the webcam is disable or not into the report will need little time to look at the drivers ,wmi to find the correct entry to pull for report. I do not have any physical devices attach to my lab however ,you can check the pnp device or camera disable registry using GPO policies applied on PC.