1 Reply Latest reply on Oct 11, 2017 7:09 AM by bblanchard

    Endpoint Security Firewall Catalog Cleanup

    bblanchard

      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.

        • 1. Re: Endpoint Security Firewall Catalog Cleanup
          bblanchard

          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))