Is there a way using an API to globally hide a fixlet?

Customizations
9

You might not be surprised to find that I couldn’t leave this alone and incorporated this logic as well:


DECLARE @CurrentSiteID INT;
DECLARE @CurrentFixletID INT;

-- 1. Declare the cursor using the CTE to safely exclude active items
DECLARE FixletCursor CURSOR FOR
    WITH CombinedFixletsExclude AS (
        -- 1. Exclude if it has at least 1 relevant computer
        SELECT DISTINCT [SiteID]
              ,[ID] As FixletID
        FROM [BFEnterprise].[dbo].[FIXLETRESULTS]
        WHERE [IsRelevant] = 1

        UNION

        -- 2. Exclude if it has at least 1 open action
        SELECT [SourceSiteID] As SiteID
              ,[SourceContentID] As FixletID
        FROM [BFEnterprise].[dbo].[ACTIONS]
        WHERE [SourceSiteID] is not NULL
          AND [IsStopped] = 0
          AND [IsDeleted] = 0

        UNION

        -- 3. Exclude if it already has an entry in the visibility table
        SELECT [SiteID]
              ,[FixletID]
          FROM [BFEnterprise].[dbo].[FIXLET_VISIBILITY]
    )
    SELECT 
          SiteNameMapTable.[SiteID]
        , ExternalFixletsTable.[ID]
    FROM [BFEnterprise].[dbo].[EXTERNAL_OBJECT_DEFS] AS ExternalFixletsTable
    INNER JOIN [BFEnterprise].[dbo].[SITENAMEMAP] AS SiteNameMapTable
        ON ExternalFixletsTable.[Sitename] = SiteNameMapTable.[Sitename]
    WHERE ExternalFixletsTable.[IsFixlet] = 1
      AND ExternalFixletsTable.[Name] LIKE '% (Superseded)%'
      -- The Logic: Only select items that DO NOT exist in your Combined Exclude list
      AND NOT EXISTS (
          SELECT 1 
          FROM CombinedFixletsExclude AS ExcludeList
          WHERE ExcludeList.[SiteID] = SiteNameMapTable.[SiteID]
            AND ExcludeList.[FixletID] = ExternalFixletsTable.[ID]
      );

-- 2. Open the cursor and begin the loop
OPEN FixletCursor;

FETCH NEXT FROM FixletCursor INTO @CurrentSiteID, @CurrentFixletID;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 3. Call the Stored Procedure for the current row
    -- We assume isVisible = 0 because we want to hide these
    EXEC dbo.update_fixlet_visibility 
        @siteID = @CurrentSiteID, 
        @fixletID = @CurrentFixletID, 
        @isVisible = 0;

    FETCH NEXT FROM FixletCursor INTO @CurrentSiteID, @CurrentFixletID;
END

-- 4. Clean up
CLOSE FixletCursor;
DEALLOCATE FixletCursor;

Obviously this is worth careful consideration especially in production. Not a bad idea to backup the DB first. But theoretically this could be run once a week in production on a schedule using a SQL agent or something.

Also the logic is such that if this hides something, and you decide to unhide it… it should stay unhidden as a result for it will still be in the visibility table.

I know session relevance isn’t easy… but it took me ~30min to write the session relevance to define the set of fixlets I want to hide in 2015. It took me 11 years to write the SQL for the same, lol.

I updated my github with this latest code: tools/SQL/BigFix_FixletsExternal_Superseded_StoredProcedureHide.sql at master · jgstew/tools · GitHub

I actually made a further refinement to this, plus wrapped it up into a fixlet: bigfix-content/fixlet/RUN_ SQLCMD - Hide Superseded Fixlets - Windows.bes at main · jgstew/bigfix-content · GitHub

2 Likes