Merging 2 Reports

1

I am looking to merge the following two reports in SCCM and keep coming across syntax issues. Novice to SQL scripting and was tasked with getting this to work.

Script 1

Select distinct SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
FROM fn_rbac_GS_SoftwareProduct(@UserSIDs) SP
JOIN fn_rbac_R_System(@UserSIDs) SYS on SP.ResourceID = SYS.ResourceID
WHERE SP.ProductName = @variable
Order by SP.ProductName, SP.ProductVersion

begin
if (@filterwildcard = '')
select distinct ProductName from fn_rbac_SoftwareProduct(@UserSIDs) order by ProductName
else
select distinct ProductName from fn_rbac_SoftwareProduct(@UserSIDs)
WHERE ProductName like @filterwildcard
order by ProductName
end

Script 2

SELECT
MAX(v_RA_System_SystemOUName.System_OU_Name0) AS [DEVICE_OU],
v_R_System.AD_Site_Name0 AS [DEVICE_SITE],
v_R_System.Netbios_Name0 AS [DEVICE_NAME],
v_R_System.User_Name0 AS [DEVICE_USERNAME],
v_GS_OPERATING_SYSTEM.Caption0 AS [DEVICE_OS_NAME],
v_GS_OPERATING_SYSTEM.Version0 AS [DEVICE_OS_VERSION],
CASE v_GS_OPERATING_SYSTEM.BuildNumber0
WHEN '18362' THEN '1903'
WHEN '17763' THEN '1809'
WHEN '17134' THEN '1803'
WHEN '16299' THEN '1709'
WHEN '15063' THEN '1703'
WHEN '14393' THEN '1607'
WHEN '10240' THEN '1507'
Else v_GS_OPERATING_SYSTEM.BuildNumber0
END AS [BUILD VERSION],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [DEVICE_MANUFACTURER],
v_GS_COMPUTER_SYSTEM.Model0 AS [DEVICE_MODEL],
CASE v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Desktop'
WHEN '2' THEN 'Desktop'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Desktop'
WHEN '5' THEN 'Desktop'
WHEN '6' THEN 'Desktop'
WHEN '7' THEN 'Desktop'
WHEN '8' THEN 'Laptop'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Laptop'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Laptop'
WHEN '15' THEN 'Desktop'
WHEN '16' THEN 'Laptop'
WHEN '17' THEN 'Desktop'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
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 'Desktop'
ELSE 'Undefined'
END AS [DEVICE_CHASSIS],
v_GS_PC_BIOS.SerialNumber0 AS [DEVICE_SERIAL_NUMBER],
v_GS_OPERATING_SYSTEM.Description0 AS [DEVICE_DESCRIPTION],
v_GS_COMPUTER_SYSTEM.Domain0 AS [DEVICE_DOMAIN],
v_GS_PC_BIOS.Name0 AS [DEVICE_BIOS_VERSION],
v_GS_PROCESSOR.Name0 AS [DEVICE_CPU],
v_GS_PROCESSOR.NumberOfCores0 AS [DEVICE_CPU_CORES],
CASE v_GS_PROCESSOR.IsHyperthreadCapable0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [CPU_HYPERTHREADING],
v_GS_PROCESSOR.NormSpeed0 AS [DEVICE_CPU_SPEED],
CASE v_GS_FIRMWARE.SecureBoot0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [DEVICE_SECURE_BOOT_ENABLED],
CASE v_GS_FIRMWARE.UEFI0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [DEVICE_UEFI_ENABLED],
ROUND (v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, -1) / 1024000 AS [DEVICE_MEMORY(GB)],
SUM (v_GS_LOGICAL_DISK.Size0) / 1024 AS [DEVICE_DISK_CAPACITY(GB)],
SUM (v_GS_LOGICAL_DISK.FreeSpace0) / 1024 AS [DEVICE_DISK_FREE(GB)],
CASE v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0
WHEN '1' THEN 'Encrypted'
WHEN '0' THEN 'Not Encrypted'
END AS [DEVICE_C-DRIVE_BITLOCKER_STATUS],
v_R_System.Client_Version0 AS [DEVICE_SCCM_CLIENT],
v_R_System.ResourceID AS [DEVICE_RESOURCE_ID]
FROM v_R_System
JOIN v_RA_System_SystemOUName ON v_R_System.ResourceID = v_RA_System_SystemOUName.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
JOIN v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID
JOIN v_GS_FIRMWARE ON v_R_System.ResourceID = v_GS_FIRMWARE.ResourceID
JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
JOIN v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
Join v_GS_LOGICAL_DISK ON v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID
JOIN v_GS_ENCRYPTABLE_VOLUME ON v_R_System.ResourceID = v_GS_ENCRYPTABLE_VOLUME.ResourceID
WHERE /* v_R_System.Netbios_Name0 = (@DevName) AND */ v_GS_LOGICAL_DISK.Caption0 = 'C:' AND v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0 = 1
Group By v_R_System.Netbios_Name0,v_R_System.AD_Site_Name0,v_R_System.Client_Version0,v_R_System.User_Name0,v_R_System.ResourceID,v_GS_OPERATING_SYSTEM.Caption0,
v_GS_OPERATING_SYSTEM.Version0,v_GS_OPERATING_SYSTEM.BuildNumber0,v_GS_OPERATING_SYSTEM.Description0,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_COMPUTER_SYSTEM.Domain0,v_GS_PC_BIOS.Name0,
v_GS_PC_BIOS.SerialNumber0,v_GS_PROCESSOR.Name0,v_GS_PROCESSOR.NumberOfCores0,v_GS_PROCESSOR.IsHyperthreadCapable0,v_GS_PROCESSOR.NormSpeed0,v_GS_FIRMWARE.SecureBoot0,
v_GS_FIRMWARE.UEFI0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0

2

Would be nice if we could see these syntax errors in order to help you.

3

It would also be helpful if you provided some sample data - and expected results. From the 2 scripts it isn't clear on what is expected by 'merging'.

You may also be making this harder - because you are using 2 table-valued functions in the first script that may actually be querying the same set of tables in the second query. If that is the case you can probably just incorporate the queries from the functions into the second query to get the final results.

Also - the first script returns 2 separate result sets...the first result is based on the parameter @variable and the second is based on @filterwildcard...not sure what this is trying to accomplish.

You can simplify the second query to:

Select Distinct
       ProductName
  From fn_rback_SoftwareProduct(@UserSIDS)
 Where ProductName Like coalesce(nullif(@filterwildcard, ''), '%');
4

Sorry about that. Here are my two datasets

Dataset0:

Select
distinct SYS.Netbios_Name0 AS [DEVICE_NAME],
SYS.User_Name0 AS [DEVICE_USERNAME],
SP.ProductName AS [PRODUCT_NAME],
SP.CompanyName AS [MANUFACTURER_NAME],
SP.ProductVersion AS [PRODUCT_VERSION],
v_GS_PC_BIOS.SerialNumber0 AS [DEVICE_SERIAL_NUMBER],
v_GS_OPERATING_SYSTEM.Caption0 AS [DEVICE_OS_NAME],
v_GS_OPERATING_SYSTEM.Version0 AS [DEVICE_OS_VERSION],
CASE v_GS_OPERATING_SYSTEM.BuildNumber0
WHEN '18362' THEN '1903'
WHEN '17763' THEN '1809'
WHEN '17134' THEN '1803'
WHEN '16299' THEN '1709'
WHEN '15063' THEN '1703'
WHEN '14393' THEN '1607'
WHEN '10240' THEN '1507'
Else v_GS_OPERATING_SYSTEM.BuildNumber0
END AS [BUILD_VERSION],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [DEVICE_MANUFACTURER],
v_GS_COMPUTER_SYSTEM.Model0 AS [DEVICE_MODEL],
v_R_System.AD_Site_Name0 AS [DEVICE_SITE],
v_R_System.Client_Version0 AS [DEVICE_SCCM_CLIENT],
v_GS_PC_BIOS.Name0 AS [DEVICE_BIOS_VERSION],
v_GS_PROCESSOR.NumberOfCores0 AS [DEVICE_CPU_CORES],
v_GS_PROCESSOR.NormSpeed0 AS [DEVICE_CPU_SPEED],
v_GS_OPERATING_SYSTEM.Description0 AS [DEVICE_DESCRIPTION],
v_GS_COMPUTER_SYSTEM.Domain0 AS [DEVICE_DOMAIN],
MAX(v_RA_System_SystemOUName.System_OU_Name0) AS [DEVICE_OU],
CASE v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'DESKTOP'
WHEN '2' THEN 'DESKTOP'
WHEN '3' THEN 'DESKTOP'
WHEN '4' THEN 'DESKTOP'
WHEN '5' THEN 'DESKTOP'
WHEN '6' THEN 'DESKTOP'
WHEN '7' THEN 'DEKSTOP'
WHEN '8' THEN 'LAPTOP'
WHEN '9' THEN 'LAPTOP'
WHEN '10' THEN 'LAPTOP'
WHEN '11' THEN ' HAND HELD'
WHEN '12' THEN ' DOCKING STATION'
WHEN '13' THEN 'ALL IN ONE'
WHEN '14' THEN 'LAPTOP'
WHEN '15' THEN 'DESKTOP'
WHEN '16' THEN 'LAPTOP'
WHEN '17' THEN 'DESKTOP'
WHEN '18' THEN 'EXPANSION CHASSIS'
WHEN '19' THEN 'SUBCHASSIS'
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 'DESKTOP'
ELSE 'Undefined'
END AS [DEVICE_CHASSIS],
ROUND (v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, -1) /1024000 AS [DEVICE_MEMORY(GB)],
SUM(v_GS_LOGICAL_DISK.Size0) /1024 AS [DEVICE_DISK_CAPACITY(GB)],
SUM(v_GS_LOGICAL_DISK.FreeSpace0) /1024 AS [DEVICE_DISK_FREE(GB)],
CASE v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0
WHEN '1' THEN 'ENCRYPTED'
WHEN '0' THEN 'NOT ENCRYPTED'
END AS [DEVICE_C-DRIVE_BITLOCKER_STATUS],
CASE v_GS_PROCESSOR.IsHyperthreadCapable0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [CPU_HYPERTHEARDING],
CASE v_GS_FIRMWARE.SecureBoot0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [DEVICE_SECURE_BOOT_ENABLED],
CASE v_GS_FIRMWARE.UEFI0
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [DEVICE_UEFI_ENABLED],
v_GS_PROCESSOR.Name0 AS [DEVICE_CPU]

FROM fn_rbac_GS_SoftwareProduct(@UserSIDs) SP

JOIN fn_rbac_R_System(@UserSIDs) SYS on SP.ResourceID = SYS.ResourceID
JOIN v_GS_FIRMWARE ON SYS.ResourceID = v_GS_FIRMWARE.ResourceID
JOIN v_GS_PC_BIOS ON SYS.ResourceID = v_GS_PC_BIOS.ResourceID
JOIN v_R_System ON SYS.ResourceID = v_R_System.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON SYS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM ON SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
JOIN v_GS_PROCESSOR ON SYS.ResourceID = v_GS_PROCESSOR.ResourceID
JOIN v_RA_System_SystemOUName ON SYS.ResourceID = v_RA_System_SystemOUName.ResourceID
JOIN v_GS_SYSTEM_ENCLOSURE ON SYS.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
JOIN v_GS_X86_PC_MEMORY ON SYS.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
Join v_GS_LOGICAL_DISK ON SYS.ResourceID = v_GS_LOGICAL_DISK.ResourceID
JOIN v_GS_ENCRYPTABLE_VOLUME ON SYS.ResourceID = v_GS_ENCRYPTABLE_VOLUME.ResourceID

WHERE SP.ProductName = @variable AND v_GS_LOGICAL_DISK.Caption0 = 'C:'

Order by SP.ProductName, SP.ProductVersion

DataSet1:

begin
if (@filterwildcard = '')
select distinct ProductName from fn_rbac_SoftwareProduct(@UserSIDs) order by ProductName
else
select distinct ProductName from fn_rbac_SoftwareProduct(@UserSIDs)
WHERE ProductName like @filterwildcard
order by ProductName
end

When I use the MAX function in Dataset0, I received a pop up to Define Query Parameters (Capture1). Whatever I enter in the Data Type and hit ok I get an error that is Capture2. When I enter the Sum function, I get the same Define Query Parameters (Capture1), and whatever I enter in the Data Type and hit ok I get an error that is Capture3.
Capture1

5

Capture2

6

Capture3

7

Still waiting for the syntax errors

8

hi

i have formatted the datasets .. so that it is nice to look at and follow
hope this helps :slight_smile: :slight_smile:

data set 0 
SELECT DISTINCT SYS.netbios_name0                                             AS 
                [DEVICE_NAME], 
                SYS.user_name0                                                AS 
                [DEVICE_USERNAME], 
                SP.productname                                                AS 
                [PRODUCT_NAME], 
                SP.companyname                                                AS 
                [MANUFACTURER_NAME], 
                SP.productversion                                             AS 
                [PRODUCT_VERSION], 
                v_gs_pc_bios.serialnumber0                                    AS 
                [DEVICE_SERIAL_NUMBER], 
                v_gs_operating_system.caption0                                AS 
                [DEVICE_OS_NAME], 
                v_gs_operating_system.version0                                AS 
                [DEVICE_OS_VERSION], 
                CASE v_gs_operating_system.buildnumber0 
                  WHEN '18362' THEN '1903' 
                  WHEN '17763' THEN '1809' 
                  WHEN '17134' THEN '1803' 
                  WHEN '16299' THEN '1709' 
                  WHEN '15063' THEN '1703' 
                  WHEN '14393' THEN '1607' 
                  WHEN '10240' THEN '1507' 
                  ELSE v_gs_operating_system.buildnumber0 
                END                                                           AS 
                [BUILD_VERSION], 
                v_gs_computer_system.manufacturer0                            AS 
                [DEVICE_MANUFACTURER], 
                v_gs_computer_system.model0                                   AS 
                [DEVICE_MODEL], 
                v_r_system.ad_site_name0                                      AS 
                [DEVICE_SITE], 
                v_r_system.client_version0                                    AS 
                [DEVICE_SCCM_CLIENT], 
                v_gs_pc_bios.name0                                            AS 
                [DEVICE_BIOS_VERSION], 
                v_gs_processor.numberofcores0                                 AS 
                [DEVICE_CPU_CORES], 
                v_gs_processor.normspeed0                                     AS 
                [DEVICE_CPU_SPEED], 
                v_gs_operating_system.description0                            AS 
                [DEVICE_DESCRIPTION], 
                v_gs_computer_system.domain0                                  AS 
                [DEVICE_DOMAIN], 
                Max(v_ra_system_systemouname.system_ou_name0)                 AS 
                [DEVICE_OU], 
                CASE v_gs_system_enclosure.chassistypes0 
                  WHEN '1' THEN 'DESKTOP' 
                  WHEN '2' THEN 'DESKTOP' 
                  WHEN '3' THEN 'DESKTOP' 
                  WHEN '4' THEN 'DESKTOP' 
                  WHEN '5' THEN 'DESKTOP' 
                  WHEN '6' THEN 'DESKTOP' 
                  WHEN '7' THEN 'DEKSTOP' 
                  WHEN '8' THEN 'LAPTOP' 
                  WHEN '9' THEN 'LAPTOP' 
                  WHEN '10' THEN 'LAPTOP' 
                  WHEN '11' THEN ' HAND HELD' 
                  WHEN '12' THEN ' DOCKING STATION' 
                  WHEN '13' THEN 'ALL IN ONE' 
                  WHEN '14' THEN 'LAPTOP' 
                  WHEN '15' THEN 'DESKTOP' 
                  WHEN '16' THEN 'LAPTOP' 
                  WHEN '17' THEN 'DESKTOP' 
                  WHEN '18' THEN 'EXPANSION CHASSIS' 
                  WHEN '19' THEN 'SUBCHASSIS' 
                  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 'DESKTOP' 
                  ELSE 'Undefined' 
                END                                                           AS 
                [DEVICE_CHASSIS], 
                Round (v_gs_x86_pc_memory.totalphysicalmemory0, -1) / 1024000 AS 
                [DEVICE_MEMORY(GB)], 
                Sum(v_gs_logical_disk.size0) / 1024                           AS 
                [DEVICE_DISK_CAPACITY(GB)], 
                Sum(v_gs_logical_disk.freespace0) / 1024                      AS 
                [DEVICE_DISK_FREE(GB)], 
                CASE v_gs_encryptable_volume.protectionstatus0 
                  WHEN '1' THEN 'ENCRYPTED' 
                  WHEN '0' THEN 'NOT ENCRYPTED' 
                END                                                           AS 
                [DEVICE_C-DRIVE_BITLOCKER_STATUS], 
                CASE v_gs_processor.ishyperthreadcapable0 
                  WHEN '1' THEN 'YES' 
                  WHEN '0' THEN 'NO' 
                END                                                           AS 
                [CPU_HYPERTHEARDING], 
                CASE v_gs_firmware.secureboot0 
                  WHEN '1' THEN 'YES' 
                  WHEN '0' THEN 'NO' 
                END                                                           AS 
                [DEVICE_SECURE_BOOT_ENABLED], 
                CASE v_gs_firmware.uefi0 
                  WHEN '1' THEN 'YES' 
                  WHEN '0' THEN 'NO' 
                END                                                           AS 
                [DEVICE_UEFI_ENABLED], 
                v_gs_processor.name0                                          AS 
                [DEVICE_CPU] 
FROM   Fn_rbac_gs_softwareproduct(@UserSIDs) SP 
       JOIN Fn_rbac_r_system(@UserSIDs) SYS 
         ON SP.resourceid = SYS.resourceid 
       JOIN v_gs_firmware 
         ON SYS.resourceid = v_gs_firmware.resourceid 
       JOIN v_gs_pc_bios 
         ON SYS.resourceid = v_gs_pc_bios.resourceid 
       JOIN v_r_system 
         ON SYS.resourceid = v_r_system.resourceid 
       JOIN v_gs_operating_system 
         ON SYS.resourceid = v_gs_operating_system.resourceid 
       JOIN v_gs_computer_system 
         ON SYS.resourceid = v_gs_computer_system.resourceid 
       JOIN v_gs_processor 
         ON SYS.resourceid = v_gs_processor.resourceid 
       JOIN v_ra_system_systemouname 
         ON SYS.resourceid = v_ra_system_systemouname.resourceid 
       JOIN v_gs_system_enclosure 
         ON SYS.resourceid = v_gs_system_enclosure.resourceid 
       JOIN v_gs_x86_pc_memory 
         ON SYS.resourceid = v_gs_x86_pc_memory.resourceid 
       JOIN v_gs_logical_disk 
         ON SYS.resourceid = v_gs_logical_disk.resourceid 
       JOIN v_gs_encryptable_volume 
         ON SYS.resourceid = v_gs_encryptable_volume.resourceid 
WHERE  SP.productname = @variable 
       AND v_gs_logical_disk.caption0 = 'C:' 
ORDER  BY SP.productname, 
          SP.productversion
DataSet1 
BEGIN 
    IF ( @filterwildcard = '' ) 
      SELECT DISTINCT productname 
      FROM   Fn_rbac_softwareproduct(@UserSIDs) 
      ORDER  BY productname 
    ELSE 
      SELECT DISTINCT productname 
      FROM   Fn_rbac_softwareproduct(@UserSIDs) 
      WHERE  productname LIKE @filterwildcard 
      ORDER  BY productname 
END
9

I was able to fix the syntax errors that came up. The issue is that I am appending a built in report in SCCM with one that I have. The one that I have works on it's own, but when I enter the Max and Sum functions, that is when I receive the error pop ups.