April 2018 ~ Learn MECM

bcl_648219923.htm

Contents
1.	All Active Windows Workstations (Laptops/Desktops) .........................................................................	5
2.	All Active Users.....................................................................................................................................	5
3.	All Active Desktops (Worldwide)...........................................................................................................	5
4.	All Active Laptops (Worldwide).............................................................................................................	5
5.	All Windows 7 x64 Systems .................................................................................................................	5
6.	All Windows 7 x86 Systems .................................................................................................................	5
7.	All Windows x64 Systems ....................................................................................................................	6
8.	All Windows x86 Systems ....................................................................................................................	6
9.	All PCs with Absolute Manage Client Application Installed ..................................................................	6
10.	All PCs with SG-APPL-SCCM AutoCAD_2012_EN AD Group............................................................	6
11.	All PCs with COUNTRIES/IN/WORKSTATIONS OU...........................................................................	6
12.	All PCs with IN-CHN AD Site................................................................................................................	6
13.	All Desktops (Worldwide) .....................................................................................................................	6
14.	All Laptops (Worldwide)........................................................................................................................	6
15.	All PCs with SCCM Client Agent Installed............................................................................................	7
16.	All PCs without SCCM Client Agent Installed.......................................................................................	7
17.	All PCs with Duplicate Hostnames .......................................................................................................	7
18.	All PCs with Obsolete Clients ...............................................................................................................	7
19.	All PCs with Internet Explorer 11 Installed ...........................................................................................	7
20.	All PCs less than 2 GB RAM ................................................................................................................	7
21.	All PCs with newly discovered from Active Directory within 30 Days...................................................	7
22.	All PCs with HP make and specific model............................................................................................	7
23.	All Active Windows Workstations (Desktops) .......................................................................................	7
24.	All Active Windows Workstations (Laptops) .........................................................................................	8
25.	All Users ...............................................................................................................................................	8
26.	All VDI Windows Workstations (Laptops/Desktops).............................................................................	8
27.	All Windows Servers.............................................................................................................................	8
28.	All Windows Servers and Workstations................................................................................................	8
29.	All Windows Workstations (Desktops)..................................................................................................	8
30.	All Windows Workstations (Laptops) ....................................................................................................	8
31.	All Windows Workstations (Laptops/Desktops)....................................................................................	9
32.	All Total Workstations Scope................................................................................................................	9
33.	All Workstations \| Windows 7 ...............................................................................................................	9
34.	All Workstations \| Windows 8 ...............................................................................................................	9
35.	All Workstations \| Windows 8.1 ............................................................................................................	9

36.	All Workstations \| Windows 10 .............................................................................................................	9
37.	All Workstations \| All x64 ......................................................................................................................	9
38.	All Workstations \| All x86 ......................................................................................................................	9
39.	All Workstations \| All Virtual..................................................................................................................	9
40.	All Workstations \| All ...........................................................................................................................	10
41.	All Workstations \| All Desktops ...........................................................................................................	10
42.	All Workstations \| All Laptops .............................................................................................................	10
43.	All Workstations \| All Laptops Dell......................................................................................................	10
44.	All Workstations \| All Laptops HP .......................................................................................................	10
45.	All Workstations \| All Laptops Lenovo ................................................................................................	10
46.	All Workstations Clients Version \| SCCM 2012 R2 RTM CU0 (5.00.7958.1000)...............................	10
47.	All Workstations Clients Version \| SCCM 2012 R2 SP1 (5.00.8239.1000) ........................................	10
48.	All Workstations Clients Version \| SCCM 2012 R2 SP1 CU2 (5.00.8239.1301)................................	11
49.	All Workstations Clients Version \| SCCM 2012 R2 SP1 CU3 (5.00.8239.1403)................................	11
50.	All Lenovo M72 Desktops...................................................................................................................	11
51.	All Lenovo T440 Laptops ....................................................................................................................	11
52.	All Mac Systems .................................................................................................................................	11
53.	All SCCM \| Console ............................................................................................................................	11
54.	All SCCM \| Distribution Points ............................................................................................................	11
55.	All SCCM \| Site Servers......................................................................................................................	11
56.	All SCCM \| Site Systems ....................................................................................................................	12
57.	All Servers \| All ...................................................................................................................................	12
58.	All Servers \| Physical ..........................................................................................................................	12
59.	All Servers \| Virtual .............................................................................................................................	12
60.	All Servers \| Windows 2003 and 2003 R2 ..........................................................................................	12
61.	All Servers \| Windows 2008 and 2008 R2 ..........................................................................................	12
62.	All Servers \| Windows 2012 and 2012 R2 ..........................................................................................	12
63.	All PCs with Absolute Manage Client Installed...................................................................................	12
64.	All PCs without Adobe CatalogExtension 3.0.10 Installed .................................................................	12
65.	All PCs without Adobe Illustrator 19.2.1 Installed...............................................................................	13
66.	All PCs without Cisco AnyconnectSecureMobilityClient 4.2.02075 Installed .....................................	13
67.	All PCs without Computrace Installed ................................................................................................	13
68.	All PCs without Symantec EndpointProtection 12.1.6867.6400 Installed ..........................................	13
69.	All PCs without SCCM Client CU3 Installed .......................................................................................	13
70.	All PCs without Microsoft OneDrive 17.3.6386.0412 Installed ...........................................................	13
71.	All PCs without Microsoft Silverlight 5.1.41212.0 Installed ................................................................	13
72.	All PCs without Bit9 Agent 7.2.2.1119 Installed .................................................................................	13
73.	All Assigned Systems .........................................................................................................................	14
74.	All Not Assigned Systems ..................................................................................................................	14

75.	All Client Systems...............................................................................................................................	14
76.	All No Client Systems .........................................................................................................................	14
77.	All Not Obsolete Systems...................................................................................................................	14
78.	All Obsolete Systems .........................................................................................................................	14
79.	All Approved Systems ........................................................................................................................	14
80.	All Not Approved Systems..................................................................................................................	14
81.	All Active Systems ..............................................................................................................................	14
82.	All Inactive Systems ...........................................................................................................................	15
83.	All PCs Heartbeat DDR Current within 23 Days.................................................................................	15
84.	All PCs Heartbeat DDR Not Current within 23 Days ..........................................................................	15
85.	All PCs Hardware Inventory Current within 23 Days ..........................................................................	15
86.	All PCs Hardware Inventory Not Current within 23 Days ...................................................................	15
87.	All PCs Software Inventory Current within 23 Days ...........................................................................	15
88.	All PCs Software Inventory Not Current within 23 Days.....................................................................	15
89.	All PCs Physical Memory Current Reported ......................................................................................	15
90.	All PCs Physical Memory Not Current Reported ................................................................................	16
91.	All PCs SCCM 2012 R2 SP1 is Installed............................................................................................	16
92.	All PCs SCCM 2012 R2 SP1 is Not Installed .....................................................................................	16
93.	All PCs with Office 365 2013 Application Installed.............................................................................	16
94.	All PCs without Office 365 2013 Application Installed........................................................................	16
95.	All PCs offline 60+ Days .....................................................................................................................	16
96.	All PCs with a heartbeat within last 30 days.......................................................................................	16
97.	All PCs without a heartbeat within last 60 days..................................................................................	17
98.	All PCs with Adobe Creative Cloud Design Standard 2015 Application Installed ..............................	17
99.	All PCs with BitLocker Encrypted .......................................................................................................	17
100. All PCs with Cisco AnyconnectSecureMobilityClient 4.2.02075 Installed .........................................		17
101. All PCs with SEP Application Installed ..............................................................................................		17
102. All PCs without SEP Application Installed .........................................................................................		17
103.	All Windows Workstations with C Drive and < 5 GB Free space ......................................................	17

1. All Active Windows Workstations (Laptops/Desktops)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","15","16","8","9","10","11","14") and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

2. All Active Users

select SMS_R_User.ResourceId, SMS_R_User.ResourceType, SMS_R_User.Name, SMS_R_User.UniqueUserName, SMS_R_User.WindowsNTDomain from SMS_R_User where SMS_R_User.UserName not like "SVC%"

3. All Active Desktops (Worldwide)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","15","16") and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Client = "1"

4. All Active Laptops (Worldwide)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "8" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "9" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "10" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "11" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "14" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Client = "1"

5. All Windows 7 x64 Systems

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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "%Windows 7%") and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

6. All Windows 7 x86 Systems

7. All Windows x64 Systems

8. All Windows x86 Systems

9. All PCs with Absolute Manage Client Application Installed

select

10. All PCs with SG-APPL-SCCM AutoCAD_2012_EN AD Group

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1 and SystemGroupName like "%\\ SG-APPL-SCCM AutoCAD_2012_EN"

11. All PCs with COUNTRIES/IN/WORKSTATIONS OU

select

and (SystemOUName like "%/COUNTRIES/IN/WORKSTATIONS")

12.All PCs with IN-CHN AD Site

select

13.All Desktops (Worldwide)

select

14.All Laptops (Worldwide)

select

15.All PCs with SCCM Client Agent Installed

select Name, SMSAssignedSites, IPAddresses, IPSubnets, ADSiteName, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName from sms_r_system where Client = 1

16.All PCs without SCCM Client Agent Installed

select Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName from sms_r_system where Client = 0 or Client is null

17.All PCs with Duplicate Hostnames

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from

SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId and R.Client = null

18.All PCs with Obsolete Clients

select

19. All PCs with Internet Explorer 11 Installed

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 inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "11.%"

20.All PCs less than 2 GB RAM

SELECT SYS.Netbios_Name0 As Computer_Name,CS.Manufacturer0, BIS.SerialNumber0 As System_SerialNumber, MEM.TotalPhysicalMemory0 As RAM FROM v_R_System SYS

JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID=SYS.ResourceID JOIN v_GS_DISK Dis on Dis.ResourceID=SYS.ResourceID

JOIN v_GS_PC_BIOS BIS on BIS.ResourceID=SYS.ResourceID where MEM.TotalPhysicalMemory0 <= 2000000

21.All PCs with newly discovered from Active Directory within 30 Days

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 inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where DATEDIFF(dd, SMS_G_System_SYSTEM.TimeStamp,GetDate()) < 30

22.All PCs with HP make and specific model

Select

SCCM_R_System.ResourceID, SCCM_R_System.ResourceType, SCCM_R_System.Name, SCCM_R_System.SCCMUniqueIdentifier, SCCM_R_System.ResourceDomainORWorkgroup,SCCM_R_System.Client from SCCM_R_System inner join SCCM_G_System_COMPUTER_SYSTEM on SCCM_G_System_COMPUTER_SYSTEM.ResourceID = SCCM_R_System.ResourceId where SCCM_G_System_COMPUTER_SYSTEM.Model = "HP EliteBook 2540p" or SCCM_G_System_COMPUTER_SYSTEM.Model = "HP Elitebook 8540w" or SCCM_G_System_COMPUTER_SYSTEM.Model = "HP ProBook 6450b"

23.All Active Windows Workstations (Desktops)

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 inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId =

SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on

SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","15","16") and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

24.All Active Windows Workstations (Laptops)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8","9","10","11","14") and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

25.All Users

select * from SMS_R_User

26.All VDI Windows Workstations (Laptops/Desktops)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.IsVirtualMachine = 'True' and SMS_R_System.OperatingSystemNameandVersion like '%Workstation%'

27.All Windows Servers

select

28.All Windows Servers and Workstations

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system where OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%' or OperatingSystemNameAndVersion like 'Microsoft Windows NT Workstation%'

29. All Windows Workstations (Desktops)

select

30.All Windows Workstations (Laptops)

select

31.All Windows Workstations (Laptops/Desktops)

select

32.All Total Workstations Scope

select

33.All Workstations | Windows 7

select

34.All Workstations | Windows 8

select

35.All Workstations | Windows 8.1

select

36.All Workstations | Windows 10

select

37.All Workstations | All x64

select

38. All Workstations | All x86

select

39.All Workstations | All Virtual

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM

S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where

SMS_R_System.IsVirtualMachine = 'True' and SMS_R_System.OperatingSystemNameandVersion like '%Workstation%'

40.All Workstations | All

select

41.All Workstations | All Desktops

select

42.All Workstations | All Laptops

select

43.All Workstations | All Laptops Dell

select

44.All Workstations | All Laptops HP

select

45.All Workstations | All Laptops Lenovo

Select

46.All Workstations Clients Version | SCCM 2012 R2 RTM CU0 (5.00.7958.1000)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion = "5.00.7958.1000" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

47. All Workstations Clients Version | SCCM 2012 R2 SP1 (5.00.8239.1000)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion = "5.00.8239.1000" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

48. All Workstations Clients Version | SCCM 2012 R2 SP1 CU2 (5.00.8239.1301)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion = "5.00.8239.1301" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

49.All Workstations Clients Version | SCCM 2012 R2 SP1 CU3 (5.00.8239.1403)

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion = "5.00.8239.1403" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

50.All Lenovo M72 Desktops

select

51.All Lenovo T440 Laptops

select

52.All Mac Systems

select

53.All SCCM | Console

select

54.All SCCM | Distribution Points

select

55.All SCCM | Site Servers

select

56.All SCCM | Site Systems

select

57. All Servers | All

select

58. All Servers | Physical

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.IsVirtualMachine = 'True') and SMS_R_System.OperatingSystemNameandVersion like 'Microsoft Windows NT%Server%'

59.All Servers | Virtual

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.IsVirtualMachine = 'True' and SMS_R_System.OperatingSystemNameandVersion like 'Microsoft Windows NT%Server%'

60.All Servers | Windows 2003 and 2003 R2

select

61.All Servers | Windows 2008 and 2008 R2

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like '%Server 6.0%' or OperatingSystemNameandVersion like '%Server 6.1%'

62.All Servers | Windows 2012 and 2012 R2

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameandVersion like '%Server 6.2%' or OperatingSystemNameandVersion like '%Server 6.3%'

63.All PCs with Absolute Manage Client Installed

select

64.All PCs without Adobe CatalogExtension 3.0.10 Installed

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%CatalogExtension%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion = "3.0.10")

65.All PCs without Adobe Illustrator 19.2.1 Installed

66.All PCs without Cisco AnyconnectSecureMobilityClient 4.2.02075 Installed

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Cisco%Anyconnect%Secure Mobility Client%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion < "4.2.02075")

67. All PCs without Computrace Installed

68. All PCs without Symantec EndpointProtection 12.1.6867.6400 Installed

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%Symantec Endpoint Protection%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion like "12.1.6867.6400")

69.All PCs without SCCM Client CU3 Installed

select

70.All PCs without Microsoft OneDrive 17.3.6386.0412 Installed

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%Microsoft%OneDrive%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion = "17.3.6386.0412")

71.All PCs without Microsoft Silverlight 5.1.41212.0 Installed

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%Microsoft%Silverlight%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion < "5.1.4%")

72.All PCs without Bit9 Agent 7.2.2.1119 Installed

SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID =

SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%Bit9%Agent%")

73.All Assigned Systems

select

74.All Not Assigned Systems

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System where SMSAssignedSites is not NULL)

75.All Client Systems

select

76.All No Client Systems

select

77. All Not Obsolete Systems

select

78. All Obsolete Systems

select

79.All Approved Systems

select

80.All Not Approved Systems

select

81.All Active Systems

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM

S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join

SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

82.All Inactive Systems

select

83.All PCs Heartbeat DDR Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId in (select ResourceID from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())<23)

84.All PCs Heartbeat DDR Not Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId not in (select ResourceID from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())<23)

85.All PCs Hardware Inventory Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 23)

86.All PCs Hardware Inventory Not Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 23)

87. All PCs Software Inventory Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) < 23 )

88. All PCs Software Inventory Not Current within 23 Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId not in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) < 23 )

89.All PCs Physical Memory Current Reported

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId where SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory is not NULL )

90.All PCs Physical Memory Not Current Reported

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId where SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory is not NULL )

91.All PCs SCCM 2012 R2 SP1 is Installed

select

92.All PCs SCCM 2012 R2 SP1 is Not Installed

select

93.All PCs with Office 365 2013 Application Installed

select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Microsoft Office 365 ProPlus%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion like "15.0%"

94.All PCs without Office 365 2013 Application Installed

select Sys.ResourceId, Sys.ResourceType, Sys.Name, Sys.SMSUniqueIdentifier, Sys.ResourceDomainORWorkgroup, Sys.Client from SMS_R_System as Sys where Sys.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Microsoft Office 365 ProPlus%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion like "15.0%")

95.All PCs offline 60+ Days

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SM S_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan <= DateAdd(dd,-60,GetDate()))

96. All PCs with a heartbeat within last 30 days

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, SMS_R_System.MACAddresses from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-30,GetDate())) and SMS_R_System.OperatingSystemNameandVersion like "% Workstation%"

97. All PCs without a heartbeat within last 60 days

select SMS_R_System.NetbiosName, SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser,

SMS_R_System.ADSiteName from SMS_R_System inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on

SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=60) and AgentName = "Heartbeat Discovery")) order by SMS_R_System.ADSiteName

98.All PCs with Adobe Creative Cloud Design Standard 2015 Application Installed

select distinct 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 inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "%CC-2015 Design Standard x64%"

99.All PCs with BitLocker Encrypted

select

100.All PCs with Cisco AnyconnectSecureMobilityClient 4.2.02075 Installed

select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Cisco%Anyconnect%Secure Mobility Client%" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion < "4.2.02075"

101.All PCs with SEP Application Installed

select

102.All PCs without SEP Application Installed

103.All Windows Workstations with C Drive and < 5 GB Free space

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 inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID =

SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and ( SMS_G_System_LOGICAL_DISK.FreeSpace < 5120 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:")

HTML to PDF PHP Converted By BCLTechnologies

Topics

Saturday, 14 April 2018

SCCM WQL Queries

Salad

Pasta