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!

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s