Monthly archives "January 2014"

Useful Configuration Manager Queries

Joe Kuster
Collection Query All 32 Bit Clients
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_G_System_COMPUTER_SYSTEM.SystemType = “X86-based PC”
Collection Query All 64 Bit Clients
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_G_System_COMPUTER_SYSTEM.SystemType = “X64-based PC”
Collection Query All systems with Configuration Manager 2012 console 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%System Center 2012 Configuration Manager Console%”
SQL Server Collection Query
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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Microsoft SQL Server 200%” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “Microsoft SQL Server 20%”
All Workstations with Solid State Drives [Intel, OCZ, Kingston]
select
sys.netbios_name0, dsk.model0, dsk.mediatype0, Name0, Size0, sys.user_name0, temp.model0
from v_r_system_valid sys
INNER JOIN v_GS_DISK dsk ON sys.resourceid = dsk.resourceid AND
dsk.MediaType0 LIKE ‘%Fixed hard disk%’ AND
(dsk.model0 LIKE ‘%intel%’ OR dsk.model0 LIKE ‘%kingston%’ OR dsk.model0 LIKE ‘%ocz%’ )
LEFT OUTER JOIN
(select GSC.Manufacturer0 as [Manufacturer], GSC.model0,GSC.Domainrole0 as [Machine Role],
GSC.Domainrole0, GSC.resourceID, v_GS_System_Enclosure.chassistypes0
from v_GS_COMPUTER_SYSTEM as GSC
join v_GS_System_Enclosure on GSC.resourceID = v_GS_System_Enclosure.resourceID
) as temp on temp.resourceID = sys.resourceID
WHERE sys.Operating_System_Name_and0  like ‘%workstation%’
All Windows Workstations
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 SMS_R_System.OperatingSystemNameandVersion like ‘Microsoft Windows NT%Workstation%’
All Lenovo Clients
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_G_System_COMPUTER_SYSTEM.Manufacturer like “Lenovo”
All Systems with HyperV Role
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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_SERVICE.DisplayName like “Hyper-V Virtual Machine Management”
All HyperV or Vmware VMs
select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName
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.Manufacturer in (“VMware, Inc.”,”Microsoft Corporation”)
(Note, may have false positive with Surface tablets)
Application Collection Structure & Reusable Queries
Use as general template:
– Adobe
    – Acrobat Pro 9
        – All Workstations with Adobe Acrobat Pro 9 [Query based membership for reporting purposes]
        – Approved for Acrobat Pro 9 [Deployments managed here through manual or query based memberships – target advertisement / deployment here]
        – Unapproved Installs of Acrobat Pro 9 [All workstations which have the software, but are not in approved group, can be used to force uninstalls or assist in true ups]
        – Acrobat Pro 9 – Pending Installations [All workstations in Approved collection, but where it has not been detected]
        – Acrobat Pro 9 – Pending Uninstallations [If desired, target uninstall advertisement / deployment here]
    – Acrobat Pro X
        – [structure repeats….]
Query examples to support above structure:
Acrobat Pro X
        – All workstations with Acrobat pro X
Limit to: HR-Workstations-All
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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like “Adobe Acrobat X Pro%”
        – Approved for Acrobat pro X
Membership: Direct Add
            – Pending Acrobat pro X Installs
Limited to: Approved for Acrobat Pro X
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_G_System_COMPUTER_SYSTEM.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.ProductName like “Adobe Acrobat X Pro%”)
        – Unapproved Acrobat Pro X Installs
Limited To: All Workstations with Acrobat Pro X
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 SMS_R_System.ResourceId not in (select ResourceId from SMS_CM_RES_COLL_HR200093)
    – Adobe Reader Uninstalls
Limited to: All Workstations with Acrobat Pro X
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 SMS_R_System.ResourceId in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like ‘Adobe Reader%’)
Application / Software Inventory Queries
Adobe Flash  (Systems without Specific Version)
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_G_System_COMPUTER_SYSTEM.Name not in (select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join
SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID =
SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%Flash Player%” and
SMS_G_System_ADD_REMOVE_PROGRAMS.Version = “10.1.82.76”)
Adobe Flash  (Systems With Any Version)
select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join
SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID =
SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%Flash Player%”
Adobe Flash  (Systems with Specific Version)
select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join
SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID =
SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%Flash Player%” and
SMS_G_System_ADD_REMOVE_PROGRAMS.Version = “10.1.82.76”
Bitlocker Status Collection Query
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_ENCRYPTABLE_VOLUME on SMS_G_System_ENCRYPTABLE_VOLUME.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ENCRYPTABLE_VOLUME.ProtectionStatus = 0
Note:
· Protection Status 0 : Protection OFF
· Protection Status 1 : Protection ON (Unlocked)
· Protection Status 2 : Protection ON (Locked)
Subnet Collection Query
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 SMS_R_System.IPSubnets = “10.10.0.1”
Note: “10.10.0.1” = Subnet Gateway
SCCM Query Active Directory AD OU Collection Query
select * from SMS_R_System where SMS_R_System.SystemOUName = “MyDomain.com/OU/SubOU”
SCCM All Laptops Collection Query
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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where   SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( “8”, “9”, “10”, “14” )
SCCM Needs Rebooted Post Patches Collection Query
2007 only?
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_PatchStatusEx on SMS_G_System_PatchStatusEx.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PatchStatusEx.LastStateName = “reboot pending”
SCCM Systems w/ IIS Enabled Collection Query
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_SERVICE on SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = “W3SVC”
All Dell 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_G_System_COMPUTER_SYSTEM.Manufacturer like “Dell%”
SCCM Workstations w/ Win7 Collection Query
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 SMS_R_System.OperatingSystemNameandVersion = “Microsoft Windows NT Workstation 6.1”
SCCM All Windows Servers Collection Query
select * from sms_r_system where OperatingSystemNameAndVersion like ‘Microsoft Windows NT%Server%’
SCCM Windows Server (Non-Core) OS Collection Query (2007 only)
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 SMS_R_System.ResourceId not in (select ResourceId from [ResourceID of Core Server Query – format should look like SMS_CM_RES_COLL_HR20005F])
SCCM Windows Server Core Collection Query
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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU = “12” OR   SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU = “13” OR   SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU = “14” OR   SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU = “42”
SCCM Physical Server Collection Query
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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = “23” or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = “17”
post1

5 Questions for MicrosoftMercenary.com

Joe Kuster

5 Questions for the Microsoft Mercenary Blog

1. Who are you?
Joe Kuster MCITP, MCTS: You can learn more about my work on LinkedIn
 
2. Do you work for Microsoft?
No, I’m a consultant who focuses solely on the Microsoft Technology stack, but I can usually be found at the monthly Microsoft Enterprise Management User Group in the Denver Microsoft building. If you are in the Denver Metro and work with, or are interested in working, with System Center, Hyper-V, App-V or similar technologies in the Microsoft lineup, I highly encourage dropping by.
3. What are your areas of focus?
I work predominately with the System Center Suite with a specialization in Configuration Manager.
4. What type of content do you hope to provide and will there be funny pictures?
Anything useful to those working in the System Center suite or Microsoft focused Private Cloud functionality. Also, yes.
5. Can I hire you?
I work for Catapult Systems, a top tier Microsoft Consulting Company with many accolades including being a highly coveted Microsoft National Systems Integrator. If you are in need of IT Consulting, especially in the Microsoft ecosystem, you can hire some amazing talent there, including myself. This is an unofficial blog, so none of my posts reflect the views or opinions of my employer.
All trademarks are rights of their respective owners.

post1