TFS4JIRA Self-Hosted < 9.5| How to update or add filters directly in the database

Owned by Damian Kilkowski (Inactive) (Deactivated)
Last updated: Mar 08, 2023
3 min read

This article applies only to TFS4JIRA Self-Hosted 9.4.1 and below.

Filters are stored in the following table: SynchronizationFilters

In addition in SyncProfiles table, the following information is stored:

  • Each issue will be synchronized when it matches - under JiraFilterMatchingInt

    • 0 - at least one filter

    • 1 - all filters

  • Each work item will be synchronized when it matches - under TfsFilterMatchingInt

    • 0 - at least one filter

    • 1 - all filters

  • example update query:

    UPDATE SyncProfiles SET JiraFilterMatchingInt = 0, TfsFilterMatchingInt = 0 WHERE guid='45aa8442-38b0-4423-99b0-1ac78da3e3be'

SynchronizationFilters columns.

HOW TO UPDATE FILTERS

  1. Identify a profile guid of which filters are supposed to be updated

    1. to find the guid please run

      SELECT guid,name FROM SyncProfiles

  2. having profile quid please filter SynchronizationFilters using the guid

    1. example query:

      SELECT * FROM synchronizationFilters :WHERE SyncProfile_Guid='45aa8442-38b0-4423-99b0-1ac78da3e3be'

  3. update query:

    2. SET - choose which columns should be updated

    3. WHERE - filter by Id column (MANDATORY) and SyncProfile_Guid (MANDATORY)

  4. Column options

    1. TypeInt column - filter section

      • 1 for Jira

      • 2 for ADO/TFS

    2. OperatorInt

      • 1 - equals

      • 2 - does not equal

      • 3 - starts with

      • 4 - doesn't start with

      • 5 - contains

      • 6 - doesn’t contain

HOW TO ADD NEW FILEDS TO FILTERS

  1. Identify a profile guid of which filters are supposed to be updated

    1. to find the guid please run SELECT guid,name FROM SyncProfiles

  2. having profile quid please filter SynchronizationFilters using the guid

    1. example query: SELECT * FROM synchronizationFilters WHERE SyncProfile_Guid='45aa8442-38b0-4423-99b0-1ac78da3e3be'

  3. INSERT query:

  4. Column options

    1. <FieldName> - field name available in the dropdown present in FILTERS section (in TFS4JIRA configuratior)

    2. <FieldId> - fileds ID in Jira or ADO/TFS

      1. examples

    3. <FieldValue> - value used to narrow down result pool, for example LABEL name or Create Date

    4. <TypeInt> column - filter section: Jira or ADO/TFS (in TFS4JIRA configuratior)

      • 1 for Jira

      • 2 for ADO/TFS

    5. <OperatorInt>

      • 1 - equals

      • 2 - does not equal

      • 3 - starts with

      • 4 - doesn't start with

      • 5 - contains

      • 6 - doesn’t contain

    6. <SyncProfile_Guid> - profile guid found with query: SELECT guid,name FROM SyncProfiles