Multiple SQL Queries and Queries Views for SCCM 2012

This summary is not available. Please click here to view the post.

List of Packages in SCCM

List of Packages  in SCCM
---------------------------------------------
SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID

SCCM Client Status report.

SCCM Client Status report.

SELECT S.Name0 as 'Computer Name', S.User_Name0 as 'User Name',
case when S.Decommissioned0='0'
then 'Not Decommissioned' when S.Decommissioned0='1'
then 'Decommissioned' end as 'Decommissinoed Status',
S.Last_Logon_Timestamp0,
Case when CS.ClientActiveStatus='1'
then 'Active' When CS.ClientActiveStatus='0'
then 'Inactive' end as 'Client Active Status'
FROM v_R_System S inner Join v_CH_ClientSummary CS on S.ResourceId=CS.ResourceID

Detection rule for Outlook and Skype for SCCM 2012

Detection rule for Outlook and Skype SCCM 2012 :


%ProgramFiles%\Microsoft Office\Office16
lync.exe

%ProgramFiles%\Microsoft Office\Office16
OUTLOOK.EXE

WQL- List Of Machines With Workstation 10.0

WQL- List Of Machines With Workstation 10.0

select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from SMS_R_System
where OperatingSystemNameandVersion like '%Workstation 10.0%'

Region wise available machines report

Region wise available machines report :-



select distinct

sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0,
Usr.Mail0,
sys.USer_domain0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0


where sys.resource_domain_or_workgr0 = 'AS'

Drivers (Disk) Space information for machines & Collection

Drivers (Disk ) Space information for machines :


Select v_R_System.name0,

v_R_System.Resource_Domain_OR_Workgr0 as Domain,
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.Caption0,
v_GS_LOGICAL_DISK.DriveType0,
v_R_System.Last_Logon_Timestamp0

from v_R_System

inner Join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID

where v_GS_LOGICAL_DISK.DriveType0 = '3'
order by v_R_System.name0

For Collection :-

SELECT distinct

SYS.Name,

v_r_system.user_name0,

sys.SiteCode,

SYS.Domain,

LDISK.Description0,

LDISK.DeviceID0,

LDISK.VolumeName0,

LDISK.FileSystem0,

v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',

LDISK.Size0,

LDISK.FreeSpace0

FROM

v_FullCollectionMembership_Valid SYS

join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID

join v_r_system on SYS.ResourceID= v_r_system.resourceID

Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid

WHERE LDISK.DriveType0 = 3 and  sys.CollectionID = 'SMS00001'