Cacher is the code snippet organizer for pro developers

/*
.SYNOPSIS
    Lists the installed software.
.DESCRIPTION
    Lists the installed software by user selection (Device, Publisher or Name).
    Supports filtering and exclusions by multiple software names using comma separated values and sql wildcards.
.NOTES
    Created by Ioan Popovici.
    Requires ufn_csv_String_Parser custom function.
    Part of a report should not be run separately.
.LINK
    https://SCCM.Zone/SW-Installed-Software-by-User-Selection
.LINK
    https://SCCM.Zone/SW-Installed-Software-by-User-Selection-CHANGELOG
.LINK
    https://SCCM.Zone/SW-Installed-Software-by-User-Selection-GIT
.LINK
    https://SCCM.Zone/Issues
*/

/*##=============================================*/
/*## QUERY BODY                                  */
/*##=============================================*/
/* #region QueryBody */

/* Testing variables !! Need to be commented for Production !! */
--DECLARE @UserSIDs            AS NVARCHAR(10)  = 'Disabled';
--DECLARE @CollectionID        AS NVARCHAR(250) = 'HUB00095';
--DECLARE @SoftwareNameLike    AS NVARCHAR(250) = 'Adobe%,WinZip%';
--DECLARE @SoftwareNameNotLike AS NVARCHAR(250) = 'Adobe Acrobat [1-8]%,7-zip%,%Fran_aise%';

/* Initialize SoftwareLike table */
DECLARE @SoftwareLike TABLE (
    SoftwareName NVARCHAR(250)
)

/* Initialize SoftwareNotLike table */
DECLARE @SoftwareNotLike TABLE (
    SoftwareName NVARCHAR(250)
)

/* Initialize InstalledSoftware table */
DECLARE @InstalledSoftware TABLE (
    Device              NVARCHAR(250)
    , Manufacturer      NVARCHAR(250)
    , DeviceType        NVARCHAR(50)
    , SerialNumber      NVARCHAR(50)
    , Publisher         NVARCHAR(250)
    , SoftwareName      NVARCHAR(250)
    , Version           NVARCHAR(50)
    , DomainOrWorkgroup NVARCHAR(100)
    , UserName          NVARCHAR(100)
    , OperatingSystem   NVARCHAR(100)
)

/* Populate SoftwareLike table */
INSERT INTO @SoftwareLike (SoftwareName)
SELECT StringValue
FROM CM_Tools.dbo.ufn_csv_String_Parser(@SoftwareNameLike, ',');    --!! Change the 'CM_Tools' database to your custom function database !!

/* Populate SoftwareNotLike table */
INSERT INTO @SoftwareNotLike (SoftwareName)
SELECT StringValue
FROM CM_Tools.dbo.ufn_csv_String_Parser(@SoftwareNameNotLike, ','); --!! Change the 'CM_Tools' database to your custom function database !!

/* Populate InstalledSoftware table */
INSERT INTO @InstalledSoftware (Device, Manufacturer, DeviceType, SerialNumber, Publisher, SoftwareName, Version, DomainOrWorkgroup, UserName, OperatingSystem)
SELECT DISTINCT
    Device              = Systems.Netbios_Name0
    , Manufacturer      = Enclosure.Manufacturer0
    , DeviceType        = (
        CASE
            WHEN Enclosure.ChassisTypes0 IN (8 , 9, 10, 11, 12, 14, 18, 21, 31, 32) THEN 'Laptop'
            WHEN Enclosure.ChassisTypes0 IN (3, 4, 5, 6, 7, 15, 16) THEN 'Desktop'
            WHEN Enclosure.ChassisTypes0 IN (17, 23, 28, 29) THEN 'Servers'
            WHEN Enclosure.ChassisTypes0 = '30' THEN 'Tablet'
            ELSE 'Unknown'
        END
    )
    , SerialNumber      = Enclosure.SerialNumber0
    , Publisher         = (
        CASE
            WHEN Software.Publisher0 IS NULL THEN '<No Publisher>'
            WHEN Software.Publisher0 = '' THEN '<No Publisher>'
            WHEN Software.Publisher0 = '<no manufacturer>' THEN '<No Publisher>'
            ELSE Software.Publisher0
        END
    )
    , SoftwareName      = COALESCE(NULLIF(Software.DisplayName0, ''), 'Unknown')
    , Version           = COALESCE(NULLIF(Software.Version0, ''), 'Unknown')
    , DomainOrWorkgroup = Systems.Resource_Domain_OR_Workgr0
    , UserName          = Systems.User_Name0
    , OperatingSystem   = OS.Caption0
FROM fn_rbac_Add_Remove_Programs(@UserSIDs) AS Software
    JOIN v_R_System AS Systems ON Systems.ResourceID = Software.ResourceID
    JOIN v_ClientCollectionMembers AS CollectionMembers ON CollectionMembers.ResourceID = Systems.ResourceID
    JOIN v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = Systems.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE AS Enclosure ON Enclosure.ResourceID = Systems.ResourceID
WHERE CollectionMembers.CollectionID = @CollectionID
    AND EXISTS (
        SELECT SoftwareName
        FROM @SoftwareLike AS SoftwareLike
        WHERE Software.DisplayName0 LIKE SoftwareLike.SoftwareName
    );

/* Use NOT LIKE if needed */
IF EXISTS (SELECT SoftwareName FROM @SoftwareNotLike)
BEGIN
    SELECT
        Device
        , Manufacturer
        , DeviceType
        , SerialNumber
        , Publisher
        , SoftwareName
        , Version
        , DomainOrWorkgroup
        , UserName
        , OperatingSystem
    FROM @InstalledSoftware AS InstalledSoftware
        WHERE NOT EXISTS (
            SELECT SoftwareName
            FROM @SoftwareNotLike AS SoftwareNotLike
            WHERE InstalledSoftware.SoftwareName LIKE SoftwareNotLike.SoftwareName
        )
END;

/* Otherwise perform a normal select */
ELSE
BEGIN
    SELECT
        Device
        , Manufacturer
        , DeviceType
        , SerialNumber
        , Publisher
        , SoftwareName
        , Version
        , DomainOrWorkgroup
        , UserName
        , OperatingSystem
    FROM @InstalledSoftware
END;

/* #endregion */
/*##=============================================*/
/*## END QUERY BODY                              */
/*##=============================================*/