Adding an integer variable to an SMS Webreport
I was asked to make a report that would show how many machines each agency has. As you know if you have read my blog before you know, I have about 45 agencies across 35 different sites and 5-7 domains. In order to keep everything straight I have a master collection that contains a collection for each agencies. These collections are based on OU's, Machine name policy and Domain if necessary. I never use Site code because of how things are laid out.
So the Collections look like this
All Commonwealth Agencies
--> Aging
-->DPW
-->DOH
-->etc....
Anyway the problem we have like most large Sites is that agencies don't always clean up AD like they should so there are always machines that no longer exist still in AD. The solution was to have the report only count the machines that where in each of the above collections that have reported to SMS though a hardware scan in the last X days. I was going to go with 60 days but the problem is that some laptops at some agencies aren't turned on every 60 days heck I have see where some went 120 days or more. Yeah I asked the same question "Then why do they have a laptop?" . But politics prevail. Anyway I wanted to be able to add a variable to the report so that the person running the report could put in any number of days they wanted. This way they could run the report against multiple day counts and compare the numbers to try and get a more accurate count of resources.
But alas I had a problem, when ever I added the variable to the report, I would get this error:
****************************************************
An error occurred when the report was run. The details are as follows:
Invalid operator for data type. Operator equals minus, type equals varchar.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 403
**********************************************************************
I asked the Guys on the email list what I could do to solve this problem. It turns out that I needed to declare the variable as an integer. Once this was done the report worked.
Michael A Dougherty told me to try this:
WHERE (v_GS_WORKSTATION_STATUS.LastHWScan >= DATEADD([DAY], - CONVERT(int, @days) , GETDATE()))
Here is the report before the changes that was failing:
******************************************************
SELECT v_Collection.Name AS Department
, COUNT(v_FullCollectionMembership.Name) AS 'Machine Count'
FROM v_CollectToSubCollect INNER JOIN
v_Collection ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID INNER JOIN
v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID INNER JOIN
v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN
v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE (v_CollectToSubCollect.parentCollectionID = 'PAC0004A')
and (v_GS_WORKSTATION_STATUS.LastHWScan >= DATEADD([DAY], - @days , GETDATE()))
GROUP BY v_Collection.CollectionID
, v_Collection.Name
ORDER BY v_Collection.Name
****************************************************
Here is the working Report:
Note: I also attached the working MOF file to this post.
****************************************************
SELECT v_Collection.Name AS Department, COUNT(v_FullCollectionMembership.Name) AS 'Machine Count'
FROM v_CollectToSubCollect INNER JOIN
v_Collection ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID INNER JOIN
v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID INNER JOIN
v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN
v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE (v_CollectToSubCollect.parentCollectionID = 'PAC0004A')
and (v_GS_WORKSTATION_STATUS.LastHWScan >= DATEADD([DAY], - CONVERT(int, @days) , GETDATE()))
GROUP BY v_Collection.CollectionID
, v_Collection.Name
ORDER BY v_Collection.Name
********************************
Hope this helps someone else.