SCCM Report Query

Question

Hello,

I'm trying to create my first custom report and I feel like I am close but am struggling to figure out the last bit. Basically what happens is when I try to add the field for CNLastOnlineTime(v_CollectionMemberClientBaselineStatus.CNlastonlinetime) it is returning duplicate rows for the same device.

If I remove these two lines, the report appears to be working as expected:

1. v_CollectionMemberClientBaselineStatus.CNlastonlinetime As [LastOnlineTime]
2. Inner Join v_CollectionMemberClientBaselineStatus on v_CollectionMemberClientBaselineStatus.MachineID = v_R_System_Valid.ResourceID

Here is my query:

v_R_System_Valid.ResourceID,

v_R_System_Valid.Netbios_Name0 AS [Computer Name],

v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],

[Top Console User]

= CASE

when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')

then 'Unknown'

Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0

End,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],

v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],

v_GS_COMPUTER_SYSTEM.Model0 AS [Model],

v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 As [IP Address],

v_GS_NETWORK_ADAPTER_CONFIGURATION.MACAddress0 As [Mac Address],

v_CollectionMemberClientBaselineStatus.CNlastonlinetime As [LastOnlineTime]

from v_R_System_Valid

inner JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID

Left JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID

INNER JOIN v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID

Left JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE on v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID

left JOIN v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID

Left JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION on v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_R_System_Valid.ResourceID

Inner Join v_CollectionMemberClientBaselineStatus on v_CollectionMemberClientBaselineStatus.MachineID = v_R_System_Valid.ResourceID

Where v_FullCollectionMembership.CollectionID = @CollectionID and v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 is not NULL

order by v_R_System_Valid.Netbios_Name0

Thank you!

Answer 1

@David Alfano
Thank you for posting in Microsoft Q&A forum.
When you do the query for v_CollectionMemberClientBaselineStatus, you may find there are many rows for one MachineID, this is the root cause of the issue.

select * from v_CollectionMemberClientBaselineStatus  

So, we can use a keyword "distinct" to remove the duplicate rows:

select distinct  
v_R_System_Valid.ResourceID,  
  
v_R_System_Valid.Netbios_Name0 AS [Computer Name],  
  
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],  
  
[Top Console User]  
  
= CASE  
  
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')  
  
then 'Unknown'  
  
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0  
  
End,  
  
  
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],  
  
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],  
  
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],  
  
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],  
  
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],  
  
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 As [IP Address],  
  
v_GS_NETWORK_ADAPTER_CONFIGURATION.MACAddress0 As [Mac Address],  
  
v_CollectionMemberClientBaselineStatus.CNlastonlinetime As [LastOnlineTime]  
  
from v_R_System_Valid  
  
inner JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID  
  
Left JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID  
  
INNER JOIN v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID  
  
Left JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE on v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID  
  
left JOIN v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID  
  
Left JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION on v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_R_System_Valid.ResourceID  
  
inner Join v_CollectionMemberClientBaselineStatus on v_CollectionMemberClientBaselineStatus.MachineID = v_R_System_Valid.ResourceID  
  
Where v_FullCollectionMembership.CollectionID =  @CollectionID and v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 is not NULL  
  
order by v_R_System_Valid.Netbios_Name0  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.