SCCM Collection Queries by Server Role

MFfn7

Rather than spew forth a bunch of sample queries, I’ll just hand you a virtual fishing rod, a case of imaginary beer, and point you to the make-believe boat.  This little procedure came in handy today with a customer I was helping.  I hope it helps you as well…

  • Device Collections
    • Create Device Collection
      • Name: Servers – WDS Servers (example)
        • Limiting collection: (whatever has servers with clients)
        • Use incremental updates for this collection (check)
        • Add Rule > Query-Rule
          • Name: 1 (or whatever you want, I’m lazy)
          • Edit Query Statement:
            • Omit duplicate rows (check)
            • Criteria tab
              • “Select” button (click it)
              • Class = Server Feature
              • Attribute = Name (click OK)
                • Is Equal To (leave as-is)
              • Click the “Value” button
                • Select an appropriate Feature Name
                • Enjoy a cold one!

SCCM Query Stuff, Combo Meal Style

coffee_cuteness

I see a fair amount of admins having trouble building SQL and WQL queries where the result set is empty, or not what they expected.  There’s a simple set of steps I follow for troubleshooting similar issues, and it goes something like this:

  1. Run the query without constraints.
  2. Run each Query Element
  3. Format the Query for Reading
  4. Check your inventory settings
  5. Check your coffee mug

In more detailed form:

Run the Query without Constraints

When you form a query, it generally follows a standard sequential form: “select WHAT, FROM WHAT, WHERE something is ___”.  The constraint here is the “WHERE” part.  If you drop that from the statement, leaving only “select WHAT, FROM WHAT” (ignore the commas, they’re for dramatic effect only), you should get a full output of whatever is hiding in those juicy tables and views.  If nothing comes back, you have nothing.  Problem solved.  Let’s try an example…

SELECT
  dbo.v_R_System.ResourceID, 
  dbo.v_R_System.AD_Site_Name0, 
  dbo.v_R_System.Name0
FROM
  dbo.v_R_System 
WHERE
  Name0 = 'FS1'

The constraint in this example is “WHERE Name0 = ‘FS1′”  If we simply delete (or comment) that part (the last two lines) it will return everything hiding in v_R_System.  The following image shows the same query with, and without, the constraint.  In the lower half (the “Results” tab section) you can see the results for each query, respectively.

q0

Run Each Query Element

Let’s use an example query like the one below…

SELECT
  dbo.v_R_System.ResourceID, 
  dbo.v_R_System.AD_Site_Name0, 
  dbo.v_R_System.Name0, 
  dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM
  dbo.v_R_System 
  INNER JOIN
    dbo.v_GS_COMPUTER_SYSTEM 
    ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
    dbo.v_GS_SYSTEM_ENCLOSURE 
    ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

If you look closely, you might notice that the statement is requesting data from more than one source. In other words, the “FROM” has more than one name after it, including some JOIN statements.  In reality, this query is doing the following:

SELECT ResourceID, AD_Site_Name0, Name0 FROM v_R_System

SELECT Manufacturer0, Model0 FROM v_GS_COMPUTER_SYSTEM

SELECT SerialNumber0 FROM v_GS_SYSTEM_ENCLOSURE

It then takes the results and combines it so that only those rows with identical ResourceID values will be displayed as a single row (a “relation”).

Put another way, in meth addict dialect, for each of the three (3) subqueries, computer with ResourceID 12345 is returned from each view/table and combined into a single row, then it moves on to the next.  The process is repeated (iterated) for every row that exists in v_R_System, where it ALSO has a matching ResourceID in the other two (2) views.

Format the Query for Reading

If you run queries in SQL Server Management Studio, or (God help you) entirely within the Query builder inside the ConfigMgr console (hold on a second, I have to stop heaving…okay, I’m back…)

q1

Building a query in the ConfigMgr query wizard is like trying to fight Mike Tyson with both arms Duct-taped behind your ankles, okay, maybe not *that* bad.  I should say Scotch Taped.

Building queries in SQL Server Management Studio is not going to do this for you either.  You still have to aim a shotgun at it and make it dance.  For example, if you use the “New View” feature (shown in the following image), the top panel is the graphical workspace, the second panel is the tabular view, and the third panel is the SQL view.  The last panel is the Results view, which will populate returned rows based on how the query executes.  But notice the SQL view just crams everything into a run-on sentence.  At least it breaks by SELECT, FROM, WHERE, etc.(even though I don’t have a WHERE yet).

q2

If I copy that section of “code” into a “New Query” window, I can beat it into submission with a steel pipe and a hammer…

SELECT
  dbo.v_R_System.ResourceID, 
  dbo.v_R_System.AD_Site_Name0, 
  dbo.v_R_System.Name0, 
  dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM
  dbo.v_R_System 
  INNER JOIN
    dbo.v_GS_COMPUTER_SYSTEM 
    ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
    dbo.v_GS_SYSTEM_ENCLOSURE 
    ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

This is absolutely NO different in terms of how the query engine will parse and process the statement.  But it makes it much easier to stare at when you’re hung over and drinking cold coffee, six months after you first created it, and can’t even remember whether you wrote it or someone else.

Check Your Inventory Settings

Another common, maybe one of THE most common, causes of an empty return set is that the inventory settings are not populating tables you need in order to get the query to work.  A common example would be a query that reads from any of the SOFTWARE related views and tables.  If Software inventory is not enabled, or not configured to actually gather information, guess what?  You won’t have anything to return from a query either.

q3

Go into your ConfigMgr admin console, crawl under Administration, and slide yourself over to “Client Settings”.  Then carefully scroll down to “Software Inventory”.  This is one place to poke around.  Another is Asset Intelligence, which you can find hiding under Assets and Compliance…

q4.PNG

Just click on “Edit Inventory Classes” to see what you have enabled (or not)…

asset_intel_form.PNG

Check Your Coffee Mug

Indeed.  One of the most common causes of systemic and situational errors is lack of cerebral stimulation.  In most cases: caffeine.  Whether you fill your tank on the kiddie stuff like 5-hour Energy Drink, or the teenage Red Bull/Monster/RockStar stuff, or the manly, rip the top off of skyscrapers and shit fire down the elevator shaft, Coffee, just make sure to fill your tank.  Sometimes it’s all the difference you need.

I hope this helped?  Thank you for reading!

 

SCCM / SQL – More Inventory Queries

Here’s a few more queries I’ve found helpful in the past week or two.  These were used on Configuration Manager 1602 and 1606 with SQL Server 2014 Sp1.  I will be taking a break for a while, but I would appreciate any feedback you have (and don’t forget to click the rating please?)

Computers with SSD’s Installed

SELECT DISTINCT
  ResourceID,
  SystemName0 AS ComputerName,
  Model0 AS Model,
  DeviceID0 AS DeviceID,
  Index0 AS [Index],
  InterfaceType0 AS Interface,
  Manufacturer0 AS Manufacturer,
  MediaType0 AS MediaType,
  Partitions0 AS Partitions,
  Size0 AS Capacity
FROM 
  dbo.v_GS_DISK
WHERE 
  Model0 LIKE '%SSD%'

Installation Counts for Operating Systems

SELECT DISTINCT 
  Caption0 AS OSName, 
  COUNT(*) AS Installs
FROM 
  dbo.v_GS_OPERATING_SYSTEM
GROUP BY 
  Caption0
ORDER BY
  Installs DESC

Machine Counts by BIOS Description

SELECT 
  DISTINCT Description0 AS BIOS,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_PC_BIOS
GROUP BY
  Description0
ORDER BY
  Machines DESC

Machine Counts by Time Zone

SELECT DISTINCT 
  CurrentTimeZone0 AS TimeZone,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_COMPUTER_SYSTEM
GROUP BY
  CurrentTimeZone0
ORDER BY
  CurrentTimeZone0

Count of Machines Online vs. Offline, by Site Server

SELECT DISTINCT
  SiteSystemName,
  ServerID,
  OnlineClients,
  OfflineClients,
  TotalClients
FROM 
  dbo.v_BgbServerCurrent
  LEFT OUTER JOIN dbo.v_BoundarySiteSystems ON
    dbo.v_BoundarySiteSystems.BoundaryID = ServerID
ORDER BY 
  SiteSystemName

Cheers!

SCCM / SQL – Find User Logins

Having some fun pulling user login information by way of the profile path inventory data collected from clients.  In this example, I’m excluding server logins, and focusing only on the profiles under C:\Users.  You can modify these however you want, but I hope they’re of some use to you.

1wearandtear

Filter by AD Site Name

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND 
  (dbo.v_R_System.AD_Site_Name0 = 'Default-First-Site-Name')

Filter by Time Window (within last 30 days)

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND 
  (DATEDIFF(dd,dbo.v_GS_USER_PROFILE.TimeStamp,GETDATE()) < 30)

Filter by Device Model

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0,
  dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
  INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM ON
  dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND
  (dbo.v_GS_COMPUTER_SYSTEM.Model0 = 'Elitebook Folio 9470m')

SCCM / SQL – Find Devices by IP Gateway

0123-ibm-630x420

Identify all the devices associated with a specified IP gateway…

SELECT DISTINCT 
  DNSHostName0 AS HostName, 
  ResourceID, 
  IPSubnet0 AS Mask, 
  MACAddress0 AS MAC, 
  IPAddress0 AS IPAddress, 
  DHCPEnabled0 AS DHCPEnabled, 
  DHCPServer0 AS DHCPServer, 
  DNSDomain0 AS DHCPDomain, 
  DefaultIPGateway0 AS Gateway
FROM 
  dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL) 
  AND (IPAddress0 NOT LIKE 'f%')
  AND (DefaultIPGateway0 = '192.168.29.1')
ORDER BY
  DNSHostName0

You can also join this to v_R_System to confirm the AD Site Name, as well as join to v_GS_Computer_System to pull manufacturer and model, or v_GS_System_Enclosure, to get the chassis type and BIOS serial number.

Note: the “(IPAddress0 NOT LIKE ‘f%’)” clause is used to filter out IPv6 rows, which cause duplicates and since the gateway wouldn’t match it avoids it anyway.  I could be incorrect on this however, so always, always, always test this yourself and adjust as you see fit.

SELECT DISTINCT 
  dbo.v_Network_DATA_Serialized.DNSHostName0 AS HostName, 
  dbo.v_Network_DATA_Serialized.ResourceID, 
  dbo.v_Network_DATA_Serialized.IPSubnet0 AS Mask, 
  dbo.v_Network_DATA_Serialized.MACAddress0 AS MAC, 
  dbo.v_Network_DATA_Serialized.IPAddress0 AS IPAddress, 
  dbo.v_Network_DATA_Serialized.DHCPEnabled0 AS DHCPEnabled, 
  dbo.v_Network_DATA_Serialized.DHCPServer0 AS DHCPServer, 
  dbo.v_Network_DATA_Serialized.DNSDomain0 AS DHCPDomain, 
  dbo.v_Network_DATA_Serialized.DefaultIPGateway0 AS Gateway, 
  dbo.v_R_System.AD_Site_Name0, 
  dbo.v_R_System.Client_Version0, 
  dbo.v_R_System.Is_Virtual_Machine0, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM 
  dbo.v_Network_DATA_Serialized 
  FULL OUTER JOIN
  dbo.v_GS_SYSTEM_ENCLOSURE ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID 
  FULL OUTER JOIN
  dbo.v_GS_COMPUTER_SYSTEM ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  FULL OUTER JOIN
  dbo.v_R_System ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_R_System.ResourceID
WHERE 
  (dbo.v_Network_DATA_Serialized.IPSubnet0 IS NOT NULL) AND 
  (dbo.v_Network_DATA_Serialized.IPAddress0 NOT LIKE 'f%') AND 
  (dbo.v_Network_DATA_Serialized.DefaultIPGateway0 = '192.168.29.1')
ORDER BY HostName

Enjoy!

 

ConfigMgr Tip #48 – Logical Disk Inventory

This is very, very, very, very, very, very, very, very, very (inhale, exhale) very, very, very, trivial stuff, but for those who find it useful, here goes (phew!)

The default Hardware Inventory setting collects data from the Win32_LogicalDisk WMI class, but it does not (by default) capture the FreeSpace property.  Why does this matter?  Well, when certain customers I know have removed all but one hair on their head trying to figure out why they can’t get a Query or Report to find those with “less than _” free space, they figure out that it’s just a checkbox that needs checking.

The first sign might be a null value for the “Free Space (MB)” property when viewing a device in the Resource Explorer.

cs4

To address, select Administration, then Client Settings.  If you have more than the standard “Default Client Settings” object, choose the appropriate one (or choose all of them, whatever makes you happy), and open the Properties for that object.

cs1

Within the Properties form, select Hardware Inventory, and then click the “Set Classes…” button…cs2

Scroll down to the “Logical Disk (SMS_LogicalDisk)” entry, and expand its properties list.  Then check the box to enable “Free Space (MB)”.  Notice that the image below shows the default state of that seting (unchecked).cs3

Click OK.

The inventory data should begin populating as soon as clients obtain the updated policy and run the next inventory scan after that.  You can force that from the Control Panel applet, or using your favorite Right-Click tools add-in from the console, or via a script, or via two sticks and some mud with a voodoo dance.  Whatever works for you.

I hope this was helpful.