cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
bblanchard
Level 10

Endpoint Security Firewall Catalog Cleanup

Jump to solution

After using adaptive mode a few times, it filled up our Firewall Catalog which makes ePO slow to respond when trying to edit objects in there.

This KB (KB80102) includes an SQL script which clears up unused entries in the HIPS catalog. Wasn't able to find something equivalent for ENS Firewall.

0 Kudos
1 Solution

Accepted Solutions
bblanchard
Level 10

Re: Endpoint Security Firewall Catalog Cleanup

Jump to solution

For those interested in the solution, i took parts of the script provided in KB80102 and changed the DB tables to reflect those of ENS:

-- Delete all non-default namednetworks that are not being used in either Catalog rules or Policy Objects

SET rowcount 10000

DELETE FROM FW_NamedNetwork WHERE

    name NOT IN ('Trusted Network', 'localhost')

    AND ID NOT IN (SELECT NAMEDNETWORKID FROM FW_Rule_LocalNetwork)

    AND ID NOT IN (SELECT NAMEDNETWORKID FROM FW_Rule_RemoteNetwork)

    AND CAST(ID AS NVARCHAR(50)) NOT IN (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)   

-- Delete all application catalog items which are duplicated (leaving the oldest) and no used in any catalog rule or firewall policy

SET rowcount 10000

DELETE FROM A1

    FROM FW_Application A1

WHERE  

    ID NOT IN (SELECT APPLICATIONID FROM FW_Rule_Application)

    AND CAST(ID AS NVARCHAR(50)) NOT IN

        (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)

    AND NOT EXISTS (SELECT 1 FROM FW_Application A2 WHERE A1.name = A2.name GROUP BY A2.name HAVING A1.lastModified = MIN(A2.lastModified))

  

  

  

  

-- Delete all executables catalog items which are duplicated (leaving the oldest) and not used in any

-- catalog rule or firewall policy

SET rowcount 10000

DELETE FROM E1

    FROM FW_EXECUTABLE E1

WHERE

    ID NOT IN (SELECT EXECUTABLEID FROM FW_ApplicationExecutable)

    AND CAST(ID AS NVARCHAR(50)) NOT IN (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)

    AND NOT EXISTS (SELECT 1 FROM FW_Executable E2 WHERE

                        E1.name=E2.name AND E1.description=E2.description

                        AND E1.filename=E2.filename AND E1.fingerprint=E2.fingerprint

                        AND E1.signerName=E2.signerName

                    GROUP BY

                        E2.name,E2.description,E2.filename,E2.fingerprint,E2.signerName

                    HAVING

                        E1.lastModified = MIN(E2.LASTMODIFIED))

0 Kudos
1 Reply
bblanchard
Level 10

Re: Endpoint Security Firewall Catalog Cleanup

Jump to solution

For those interested in the solution, i took parts of the script provided in KB80102 and changed the DB tables to reflect those of ENS:

-- Delete all non-default namednetworks that are not being used in either Catalog rules or Policy Objects

SET rowcount 10000

DELETE FROM FW_NamedNetwork WHERE

    name NOT IN ('Trusted Network', 'localhost')

    AND ID NOT IN (SELECT NAMEDNETWORKID FROM FW_Rule_LocalNetwork)

    AND ID NOT IN (SELECT NAMEDNETWORKID FROM FW_Rule_RemoteNetwork)

    AND CAST(ID AS NVARCHAR(50)) NOT IN (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)   

-- Delete all application catalog items which are duplicated (leaving the oldest) and no used in any catalog rule or firewall policy

SET rowcount 10000

DELETE FROM A1

    FROM FW_Application A1

WHERE  

    ID NOT IN (SELECT APPLICATIONID FROM FW_Rule_Application)

    AND CAST(ID AS NVARCHAR(50)) NOT IN

        (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)

    AND NOT EXISTS (SELECT 1 FROM FW_Application A2 WHERE A1.name = A2.name GROUP BY A2.name HAVING A1.lastModified = MIN(A2.lastModified))

  

  

  

  

-- Delete all executables catalog items which are duplicated (leaving the oldest) and not used in any

-- catalog rule or firewall policy

SET rowcount 10000

DELETE FROM E1

    FROM FW_EXECUTABLE E1

WHERE

    ID NOT IN (SELECT EXECUTABLEID FROM FW_ApplicationExecutable)

    AND CAST(ID AS NVARCHAR(50)) NOT IN (SELECT SETTINGVALUE FROM EPOPolicySettingValues WHERE SettingValue IS NOT NULL)

    AND NOT EXISTS (SELECT 1 FROM FW_Executable E2 WHERE

                        E1.name=E2.name AND E1.description=E2.description

                        AND E1.filename=E2.filename AND E1.fingerprint=E2.fingerprint

                        AND E1.signerName=E2.signerName

                    GROUP BY

                        E2.name,E2.description,E2.filename,E2.fingerprint,E2.signerName

                    HAVING

                        E1.lastModified = MIN(E2.LASTMODIFIED))

0 Kudos