Hardware 01A - Summary Report

[Garrett804]

I've recently discovered something that has me wondering if the reports I've been pulling for years are even accurate.

 

I run a simple Hardware 01A Asset Intelligence report and export it into excel to manipulate and do lookups against other data. Recently I discovered a machine that was not being pulled down in the report but is in my system and in good health with inventory data.

 

Here you can see that the search in excel for the machine comes up blank and it can't find it.

 

So I went into SQL and looked through the Tables to see if it was in fact in the table that Microsoft has the report running against. (dbo.V_R_System) Sure enough it is listed in the table but for some reason just doesn't come up on the SSRS report though.

 

I've been trying to find any related issues online but have yet to come across anything to point me in any direction. Has anyone else ever noticed the built-in MS reports not actually pulling all of the data?

 

[Garrett804]

Ok in doing some more research and getting one of our SQL people involved we were able to figure out that it is not showing all the machines due to the way the report is joined.

 

Here is the MS Query for the Hardware Summary report.

select distinct
v_R_System_Valid_Alias.ResourceID,
v_R_System_Valid_Alias.Netbios_Name0 AS [Computer Name],
v_R_System_Valid_Alias.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site_Alias.SiteName as [sMS Site Name],
[Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0 = '-1')
then @UnknownLoc
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0
End,
v_GS_OPERATING_SYSTEM_Alias.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM_Alias.CSDVersion0 AS [service Pack Level],
v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.SerialNumber0 AS [serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM_Alias.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM_Alias.Model0 AS [Model],
v_GS_X86_PC_MEMORY_Alias.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_GS_PROCESSOR_Alias.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
from fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_GS_LOGICAL_DISK_Alias.ResourceID )
where v_GS_LOGICAL_DISK_Alias.ResourceID =v_R_System_Valid_Alias.ResourceID and
v_FullCollectionMembership_Alias.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(v_GS_LOGICAL_DISK_Alias.FreeSpace0)
from fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_GS_LOGICAL_DISK_Alias.ResourceID )
where v_GS_LOGICAL_DISK_Alias.ResourceID =v_R_System_Valid_Alias.ResourceID and v_FullCollectionMembership_Alias.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias
inner join fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) v_GS_OPERATING_SYSTEM_Alias on (v_GS_OPERATING_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
left join fn_rbac_GS_SYSTEM_ENCLOSURE_UNIQUE(@UserSIDs) v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias on (v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) v_GS_COMPUTER_SYSTEM_Alias on (v_GS_COMPUTER_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_X86_PC_MEMORY(@UserSIDs) v_GS_X86_PC_MEMORY_Alias on (v_GS_X86_PC_MEMORY_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_PROCESSOR(@UserSIDs) v_GS_PROCESSOR_Alias on (v_GS_PROCESSOR_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
left join fn_rbac_Site(@UserSIDs) v_Site_Alias on (v_FullCollectionMembership_Alias.SiteCode = v_Site_Alias.SiteCode)
LEFT join fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias on (v_GS_LOGICAL_DISK_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID) and v_GS_LOGICAL_DISK_Alias.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM_Alias.WindowsDirectory0,1,2)
left join fn_rbac_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP(@UserSIDs) v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
Where v_FullCollectionMembership_Alias.CollectionID = @CollectionID
Order by v_R_System_Valid_Alias.Netbios_Name0

 

 

Once I changed out the Inner Join with a Left join the report shows all the machines as it should. In looking into the functions we found that the fn_rbac_GS_OPERATING_SYSTEM did not have the machine BSL-XOM-BR-007 in it with any data thus not allowing it to join up to the report. I'm not sure if it was intended by MS to only give you a list of the machines that they have complete data on or not but for me I would prefer to have a report that shows me everything in a collection no matter how much of the data the system currently has.

[GarthMJ]

I recommend that you post this suggestion to the Connect.microsoft.com site. This is the only way it will get fixed. I would also post the link back here so that others can vote it up.

[Garrett804]

I'm not sure if I posted it in the right place on there or not https://social.technet.microsoft.com/Forums/en-US/5958f5d9-bca8-49d2-8b5b-a588685ac486/builtin-sccm-report-issue-found?forum=configmanagergeneral

 

I'm just glad I have a solution finally to something that's been bugging me for the past few days and can share it with the rest of you.

[GarthMJ]

I would post it here. https://connect.microsoft.com/ConfigurationManagervnext, the TechNet forums will not help get thing fixed.