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.

Project: Computer Inventory Collection (Part 2)

Warning: My brain is thoroughly fried from two consecutive days in a shopping mall, bad weather, an annoying former cell plan provider, and a busted tooth.  I may tend to ramble.  Just drink something and it’ll be over soon.

Aaaaaannnnnd, Go!

In part 1 of this endeavor, I covered a bit of the pre-staging side of inventory data collection.  I didn’t go that deep obviously, and I’m certainly not going to dive into the  realm of I/O Differential or any financial stuff.  But I do need to revisit something I mentioned earlier, just for a bit, in the area of basic data structures, and how WMI fits within it.  I will pick back up on the final leg of this in part 3 (importing the data into a database).

Grinding-Gears

Some aspects of a computer device involve multiple instances of a given class instance, while other are ultimately a single instance with multiple attributes (not that each instance of a multiple result doesn’t also have multiple attributes, but please, keep drinking and I’ll keep blabbering, trust me…).  For example, when gathering Windows Services (Win32_Service), you’re looking for rows of instances (each being a service), and compiling the Name, PathName, StartMode, Status, and so on.

However, for Operating System (Win32_OperatingSystem), you’re almost always looking for one instance per device.  Granted, a physical host can potentially be associated with multiple virtual guest machines, but even in such a scenario, the guest machines will likely have distinctly different hostnames than the physical host.  So, in this class instance, you’re likely going to collect one instance (pardon the redundancy for a bit), and grab a ton of its attributes (Name, InstallDate, SystemType, OperatingSystemSKU, CSDVersion, etc.)

For that reason, I decided to treat such classes as I would other collectives such as Win32_Product, Win32_LogicalDisks, and so on.  So, rather than go nuts with building in an abstraction layer of configuration settings (another XML file with names of which attributes to gather), I’m going with the shotgun+liquor+ATV+explosives approach and just grab it all.

In other words, my database table structure will contain all of the attributes for the respective class, and tack on a unique row ID column and datestamp column.  For entertainment value, I also tacked on a “ComputerName” column.  I’d use “DeviceID” but it’s used by the Win32_DiskDrive class already, so I’d rather stay out of it’s way.  For storage and index efficiency I’d recommend converting Boolean values into integers, and larger integers (like TotalPhysicalMemory) into BigInt or something like that, or just pre-chew them like momaa birds do and crunch 434234234234 into 4096 or something less bloaty.  Precision should be your guide, so don’t let me tell you which is best.

At this point, my client data collection script (dpms_inventory.vbs) reads from a configuration XML file for items which are single-instance oriented, like Win32_OperatingSystem, Win32_BIOS, and Win32_SystemEnclosure.  The others, like Win32_NetworkAdapter, Win32_LogicalDisk, Win32_Product, and Win32_Service, will just suck it all in like a drunk Senator at a Vegas strip club.

The other XML configuration files I will use are for controlling collecting Registry and File instances, and another for data collection beyond WMI, such as special Folders, Shortcuts, and so on.

Boiling this all down

When pulling inventory, I would start with a basic footprint and build on as needed.  No need to go to the extent of what Microsoft System Center Configuration Manager collects, at least not initially.  Some of what is technically identified as “hardware” related (see link) I questionable, such as Disk Partitions, which are not physical, hence “hard” in “hardware” and well, whatever.  I decided to “normalize” the relations by splitting those things out as “system” rather than “hardware”.  And even “shares” seem to fall on the fence a bit (see link).  Call me nit-picky.

  • Hardware Inventory:
  • System Inventory:
    • Win32_OperatingSystem
    • Win32_LogicalDisk
    • Win32_DiskPartition
    • Win32_NetworkAdapter
    • Win32_Service
  • Software Inventory:
    • Win32_Product

And you thought all this techy nerdy geeky stuff was going to be all techy, nerdy and geeky.  Pfffft!  Anyhow, I know this is dry, so I’ll wrap it up.

In the end, as my script churns on a given device, it will eventually dump multiple inventory output files.  Each will be in XML form (DOM-style, with fries and ketchup).  Each will be uploaded to a central share, and from there, will be picked by another process like those nasty-looking bots in the Matrix, which unplugged Neo and flushed his ass down the toilet of reality.

Why the Two-Step?

Why don’t large arenas just open every portal and let everyone rush in as they please?  Why don’t movie theaters and night clubs do the same?  Because humans need some order in a chaotic world.  It also helps with insuring things flow in a predictable manner and therefore provide a more manageable “model” which can be maintained and optimized based on predictive analysis.  Big words go with beer like peas and shovels.  Or is it carrots and hammers?  I can never get those right.

Cheers. I will wrap up part 3 soon.