Answered by:
Custom ChassisType Report - wrong counts

Question
-
Hi there
I have a query which is showing incorrect count results and wonder if someone can help me see where I have gone wrong please. What I am trying to do is to offer a count of Chassis Types based upon a selection in SSRS on Collection, Manufacturer, OS Version so that I can include this in a Pie Chart. What I am seeing is the count being skewed by the number of collections in which the client appears.
Many thanks in advance
SELECT count (*) as ChassisCount ,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
FROM
v_GS_SYSTEM
INNER JOIN v_GS_SYSTEM
ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_SYSTEM.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM
ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID
INNER JOIN v_FullCollectionMembership
ON v_FullCollectionMembership.ResourceID = v_GS_SYSTEM.ResourceID
WHERE
(v_GS_SYSTEM_ENCLOSURE.Manufacturer0 = @Manufacturer0 or @Manufacturer0 = 'All')
AND (v_GS_OPERATING_SYSTEM.Version0 = @Version0 or @Version0 = 'All')
AND (v_FullCollectionMembership.CollectionID = @CollectionID or @CollectionID = 'All')
GROUP BY V_GS_SYSTEM_ENCLOSURE.ChassisTypes0
HAVING COUNT(*) >=1Friday, March 2, 2012 11:28 AM
Answers
-
Hi folks Count (Distinct was the way to go
- Marked as answer by Jason W Wallace Thursday, March 8, 2012 11:16 PM
Thursday, March 8, 2012 11:16 PM
All replies
-
Is it too high or too low? Why have the "Count(*) >=1"?
http://www.enhansoft.com/
Friday, March 2, 2012 9:07 PM -
Hi there Garth
The count that I am getting is equal to the number of collections that the system is in, so I'm sure it's related to the collection logic - just cannot see it myself
Why the >= 1, well good question - I am planning to put a nice big CASE statement in there to show all 25 chassis descriptions and then to squirt that into a pie chart on a report. With this in mind I am looking to exclude 0 values - but then in writing the sentance I can see the hole in my logic on that one
Sunday, March 4, 2012 9:56 AM -
So there are a few things wrong with this query..
- Nowhere in the query above are you looking at dbo.v_GS_SYSTEM_ENCLOSURE (SE) and therefore ChassisType, I assume it is a typo/Cut and paste error
- v_GS_SYSTEM is not needed at all and can be removed, move everything over to SE view
- Having count(*) >=1 is redundant, anything with a count of Null (0) will not be displayed anyways.
Now for your real issue, Why your count are “equal to the number of collections that the system”, the answer is, it is because of the way to have written your query. You have told it to list all systems in all collections and count the Chassistype of each system within each collection. What you want to do is set the Default to the all systems collect instead “SMS00001”, this will solve your problem.
BTW here is what the query should look like: http://tinyurl.com/7xot7he
http://www.enhansoft.com/
Sunday, March 4, 2012 5:30 PM -
I had a similar problem with chassis types.. I found that clients were reporting twice when it came to Docking station and Laptops.
I already created a Chassis Case type in SQL a while ago I'll see if I can find it.
http://joejoeinc.com/2012/03/sccm-sql-count-of-chassis-type.html
- Proposed as answer by JoeJoeInc Friday, March 9, 2012 11:30 AM
Monday, March 5, 2012 4:28 AM -
Hi Joe
Thanks for the reply. I used an almost identical version of your report
Select Case SE.ChassisTypes0
When 1 Then 'Other'
When 2 Then 'Unknown'
When 3 Then 'Desktop'
When 4 Then 'Low Profile Desktop'
When 5 Then 'PizzaBox'
When 6 Then 'Mini-Tower'
When 7 Then 'Tower'
When 8 Then 'Portable'
When 9 Then 'Laptop'
When 10 Then 'Notebook'
When 11 Then 'Handheld Device'
When 12 Then 'Docking Station'
When 13 Then 'All-In-One'
When 14 Then 'Sub-Notebook'
When 15 Then 'Space Saving'
When 16 Then 'Lunch Box'
When 17 Then 'Main System Chassis'
When 18 Then 'Expansion Chassis'
When 19 Then 'Sub-Chassis'
When 20 Then 'Bus Expansion Chassis'
When 21 Then 'Peripheral Chassis'
When 22 Then 'Storage Chassis'
When 23 Then 'Rack-Mount Chassis'
When 24 Then 'Sealed PC'
Else 'Unknown'
End 'Chassis Type',
Count(*) 'Chassis Count'From v_GS_System_Enclosure SE
Join v_R_System SD on SE.ResourceID = SD.ResourceID
Join V_GS_Operating_System OS on SE.ResourceID = OS.ResourceID
Join v_FullCollectionMembership CM on SE.ResourceID = CM.ResourceID
Where SE.Manufacturer0 LIKE @Manufacturer0
AND OS.Version0 LIKE @Version0
AND CM.CollectionID LIKE @CollectionIDGroup By SE.ChassisTypes0
So, what we should see is one record in SE for each ResourceID, one record in OS for each ResourceID. Now if I enter in SMS% for CollectionID what I see is (because the system is in 3 collections) the count going up to 3 instead of remaining at 1 for example.
What I want to achieve is that we can slice and dice the data by Manufacter, OS Version, CollectionID or be able to offer a null parameter and show everything
Tuesday, March 6, 2012 10:49 AM -
Hi folks Count (Distinct was the way to go
- Marked as answer by Jason W Wallace Thursday, March 8, 2012 11:16 PM
Thursday, March 8, 2012 11:16 PM