This post describes the troubleshooting steps where one of the Software Update Reports was not showing any data. When we try to run the Compliance 5 Report (Vendor/ month /year) it does not return anything.
We checked the same report for various dates and collection but didn’t help.
The first thing to try is to check the Query for the report in the SQL Management Studio and check if it returns anything.
So hardcoded the query for Collection ID: SMS00004, Vendor: Microsoft, Year: 2012
2. Temp CI Table creation
3. Fetching the data from the Views based on join info from Temp CI
So when we ran the above query against the database we did not get any record.
- From this one thing was clear that there something wrong with the data being not present in the database or some other thing was not allowing the query to work properly.
- So the next thing to be checked was to check the tables involved in it and check if they do contain any data.
All of the above queries returned the data. So there was something serious to be looked at and a more level of research was required to know how the data is being fetched by the query.
So looking at the query it could be divided into three parts:
2. Building of the Temp CI table
It contains the CIs that will be within the date mentioned, Vendor name and product.
3. Fetching the data from the above views based on the join info from the CIs returned in the Temp CI table.
So the troubleshooting began. The declaration part was fine. We started to check with the creation of the Temp CI table creation.
So after the Temp CI table creation we wrote the following query:
But to our surprise there were no records returned.
This narrowed the problem down where we know that the records are not inserted in the Temp CI table and hence joining against these is rendering us with no records.
Now the point was how the records are inserted in this temp table. We see the query is:
-We see the query simply checks for the Date, Vendor ID, Product ID and Class ID.
- We checked the vendor information. The declaration query for vendor ID is:
So to check what is the actual value getting inserted in the @vendorID we ran the query:
This query returned two values:
-This is not desired as the variable can store only one value. When I checked for the same query against my lab it only returned one value 35.
- So we wrote print @vendorID after the declaration and it displayed 302.
- This meant that the first value was overridden by the second value.
- So the next thing to be tested was What if we hardcode VendorID as 35 and check if the query returned anything.
So the change made in the Temp CI table creation was:
Temp CI Table creation
-And this worked, the records were returned by the query ‘select * from @CI’.
-Also the whole query worked and returned the records.
-So cloned this query to be hardcoded for Microsoft as a Vendor and it worked perfectly fine.
But it still left us with the following questions:
- Why did the VendorID assignment query returned two records?
- From where does the data populate in v_CategoryInfo so that we can check what is responsible for this?
Both the questions were answered once we look into the Software update point component-> Products.
When checked here we found that there were two Microsoft categories. One was the default which contains all the products and the second one was created via SCUP which contained only Lync 2010.
So that was the reason why we were getting two records for the query:
And the problem was it stored the second value 302 and hence there were no records returned.
Hope it helps !!
Umair Khan | Support Escalation Engineer | Microsoft System Center ConfigMgr
This posting is provided "AS IS" with no warranties and confers no rights.