This is easier said than done, because this table has a sequence number, manyversions, and other values and entries in this table do not exist unless an item has already been hidden.
I went poking around and figured out there is a stored procedure called dbo.update_fixlet_visibility you can call that will handle this for you, if you pass it in the SiteID and FixletID you want to hide.
The problem is, you cannot easily get SiteID and FixletID of content to hide.
This SQL query will get you this by joining 2 tables:
/****** SideID, FixletID of Superseded Fixlets ******/
SELECT
SiteNameMapTable.[SiteID]
, ExternalFixletsTable.[ID] AS [FixletID]
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)%'
Then this SQL Query will give you the ones that are not already hidden by checking that they do not already have entries in the visibility table:
/****** SiteID, FixletID for External Fixlets that are Superseded but NOT hidden ******/
SELECT
SiteNameMapTable.[SiteID]
, ExternalFixletsTable.[ID] AS [FixletID]
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)%'
AND NOT EXISTS (
SELECT 1
FROM [BFEnterprise].[dbo].[FIXLET_VISIBILITY] AS VisibilityTable
WHERE VisibilityTable.[SiteID] = SiteNameMapTable.[SiteID]
AND VisibilityTable.[FixletID] = ExternalFixletsTable.[ID]
)
Then this complicated SQL Cursor will take all of the results from the above query and feed them into the stored procedure that will hide the content for you:
/****** Call Stored Procedure to hide all Superseded Content ******/
DECLARE @CurrentSiteID INT;
DECLARE @CurrentFixletID INT;
-- 1. Declare the cursor using your "missing items" query
DECLARE FixletCursor CURSOR FOR
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)%'
AND NOT EXISTS (
SELECT 1
FROM [BFEnterprise].[dbo].[FIXLET_VISIBILITY] AS VisibilityTable
WHERE VisibilityTable.[SiteID] = SiteNameMapTable.[SiteID]
AND VisibilityTable.[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
EXEC dbo.update_fixlet_visibility
@siteID = @CurrentSiteID,
@fixletID = @CurrentFixletID,
@isVisible = 0;
-- Fetch the next row
FETCH NEXT FROM FixletCursor INTO @CurrentSiteID, @CurrentFixletID;
END
-- 4. Clean up
CLOSE FixletCursor;
DEALLOCATE FixletCursor;
Would I recommend this approach in production? Not really, I’d much rather this only hide content that has no relevant computers that have reported in the last 30 days and no open actions, but that would make this even more complicated to do and this is already quite complicated.
That said, this was an interesting exercise.
