Modifying SCCM reports

Question

Supplied within SCCM reports is an SQL statement that grabs the list of computers with given network card.

SELECT SYS.Name0, Netcard.Description0,Netcard.MACAddress0, Netcard.Manufacturer0 
FROM fn_rbac_R_System(@UserSIDs)  SYS, fn_rbac_GS_NETWORK_ADAPTER(@UserSIDs)  Netcard  
WHERE SYS.ResourceID = Netcard.ResourceID AND Netcard.Description0 LIKE @variable  
ORDER BY SYS.Name0

I want to add the computer models to this report. How should I modify the sql statement?

Thank you

*EDIT @SQLChao Is there anything I should be looking into the datasets?

Here is a lengthy SQL query pull out many details for a group of computers in sccm.

This would be what I want to add to the query above, tho this query seems to use a somewhat more complex syntax.

v_GS_COMPUTER_SYSTEM_Alias.Model0 AS [Model]

Full query here.

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 v_GS_OPERATING_SYSTEM v_GS_OPERATING_SYSTEM_Alias on (v_GS_OPERATING_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)  
 left join v_GS_SYSTEM_ENCLOSURE_UNIQUE v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias on (v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)  
 LEFT join v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM_Alias on (v_GS_COMPUTER_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)  
 LEFT join v_GS_X86_PC_MEMORY v_GS_X86_PC_MEMORY_Alias on (v_GS_X86_PC_MEMORY_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)  
 LEFT join v_GS_PROCESSOR v_GS_PROCESSOR_Alias on (v_GS_PROCESSOR_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)  
 inner join v_FullCollectionMembership 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 v_GS_LOGICAL_DISK 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 v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP 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

I have an SCCM database here. I looked at the tvf's referenced in your query and model is in neither. Maybe find a report that does have the model and post that code as well. — SQLChao, Dec 17 '19 at 16:06
@SQLChao Thanks for the quick reply. I'll find a report that does select the computer models and post-it asap. — Matthieu, Dec 17 '19 at 16:11

score 0 · Answer 1 · answered Dec 18 '19 at 09:23

It should work with something like

SELECT 
    SYS.Name0, 
    SYS.Model0, 
    Netcard.Description0,
    Netcard.MACAddress0, 
    Netcard.Manufacturer0 
FROM 
    (
    SELECT
        S.ResourceID, 
        S.Name0, 
        C.Model0 
    FROM
        fn_rbac_R_System(@UserSIDs) S
    LEFT JOIN 
        V_GS_COMPUTER_SYSTEM C
    ON 
        S.ResourceID = C.ResourceID
    ) SYS,
    fn_rbac_GS_NETWORK_ADAPTER(@UserSIDs)  Netcard  
WHERE 
    SYS.ResourceID = Netcard.ResourceID 
AND 
    Netcard.Description0 LIKE @variable  
ORDER BY 
    SYS.Name0

It is also possible to left join after the whole old query:

SELECT 
    t1.*, 
    c.Model0 
FROM 
    (
    SELECT 
        SYS.ResourceID, 
        SYS.Name0, 
        Netcard.Description0,
        Netcard.MACAddress0, 
        Netcard.Manufacturer0 
    FROM 
        fn_rbac_R_System(@UserSIDs) SYS, 
        fn_rbac_GS_NETWORK_ADAPTER(@UserSIDs) Netcard  
    WHERE 
        SYS.ResourceID = Netcard.ResourceID 
    AND 
        Netcard.Description0 LIKE @variable  
    ) t1
LEFT JOIN
    V_GS_COMPUTER_SYSTEM c
ON 
    t1.ResourceID = c.ResourceID
ORDER BY t1.Name0

I am not SQL expert enough to know which one would be preferable in this case, but both should be acceptably fast for a normal sccm db.

Modifying SCCM reports

1 Answers1