SQLTeam.com | Weblogs | Forums

Merging 2 Reports

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

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

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, ''), '%');