Case Study: Compliance 5 Report does not return anything even if the data is present in the database.


Hi Folks,

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

1. Declaration

 
set nocount 
on
declare @FromDate datetime; set 
@FromDate=convert(datetime, '2012', 20)
declare @ToDate datetime; set 
@ToDate=dateadd(month, 12 , @FromDate)
declare @VendorID int; select 
@vendorID=CategoryInstanceID from 
v_CategoryInfo where 
CategoryTypeName='Company' and 
CategoryInstanceName='Microsoft'
declare @ProductID int; set 
@ProductID=0
declare @ClassID int; set 
@ClassID=0

 

2. Temp CI Table creation

 
declare @CI table(CI_ID int primary 
key)
insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 and ci.DateRevised>=@FromDate and ci.DateRevised<@ToDate
 and (@VendorID=0 or exists(select 
1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@VendorID))
 and (@ProductID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and 
CategoryInstanceID=@ProductID))
 and (@ClassID=0 or exists(select 1 
from v_CICategories_All where CI_ID=ci.CI_ID 
and CategoryInstanceID=@ClassID))

 

3. Fetching the data from the Views based on join info from Temp CI

 
select
 Vendor=ven.CategoryInstanceName,
 
UpdateClassification=cls.CategoryInstanceName,
 ArticleID,
 BulletinID,
 Title,
 Approved=case 
when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID='SMS00004') then '*' else 
'' end,
 Present=NumPresent,
 Missing=NumMissing,
 NotApplicable=NumNotApplicable,
 Unknown=NumUnknown,
 Total=NumTotal,
 PCompliant=convert(numeric(5,2), 
isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),
 PNotCompliant=convert(numeric(5,2), 
isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),
 PUnknown=convert(numeric(5,2), 
isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),
 CollectionID='SMS00004',
 UniqueUpdateID=CI_UniqueID,
 InformationURL=InfoURL
from @CI ci
left join v_UpdateSummaryPerCollection cs 
on cs.CI_ID=ci.CI_ID and cs.CollectionID='SMS00004'
left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID
left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'
left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and 
cls.CategoryTypeName='UpdateClassification'
order by 1, 2, 3

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.
 
select * from v_CategoryInfo
 
select * from v_UpdateInfo
select CI_ID from v_CICategories_All
select CI_ID from v_UpdateCIs

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:

 1.    Declaration

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:

select * from @CI

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:

 
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 and ci.DateRevised>=@FromDate and 
ci.DateRevised<@ToDate
 and (@VendorID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=@VendorID))
 and (@ProductID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=@ProductID))
 and (@ClassID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))

-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:

 
select @vendorID=CategoryInstanceID from v_CategoryInfo where 
CategoryTypeName='Company' and CategoryInstanceName='Microsoft'

So to check what is the actual value getting inserted in the @vendorID we ran the query:

select CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Company' 
and CategoryInstanceName='Microsoft'

This query returned two values:

35

302

 

-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

 
declare @CI table(CI_ID int primary key)
insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 and ci.DateRevised>=@FromDate and 
ci.DateRevised<@ToDate
 and (@VendorID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=35))
 and (@ProductID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=@ProductID))
 
 and (@ClassID=0 or exists(select 1 from v_CICategories_All where 
CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))

-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:

  1. Why did the VendorID assignment query returned two records?
  2. 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:

select CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Company' 
and CategoryInstanceName='Microsoft'

 

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  

Disclaimer:
This posting is provided "AS IS" with no warranties and confers no rights.

Comments (0)

Skip to main content