Filter Logic broken

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

Version is 8.3 or 8.4

As per my old filter the users being returned are around 12000 and in new code I made few changes and the expected result is either the users being returned are same as pervious code or more than previous code but the users being returned are around 5000 I don’t where the logic is broken.

Old code :
Filter ServiceAccount = Filter.ne(“employeeType”, “Service Account”);
Filter terminatedUsers = Filter.eq(“status”, “Terminated”);
Filter activeLinks = Filter.eq(“links.iiqDisabled”, false);
Filter filter = Filter.and(ServiceAccount, terminatedUsers, activeLinks);
QueryOptions queryOptions = new QueryOptions();
queryOptions.addFilter(filter);

New code :
Date date = new Date();
long oneDayMinus = date.getTime() - (24 * 60 * 60 * 1000);

Filter terminationDate = Filter.and(Filter.notnull(“CustomAttribute_TermDate”),Filter.le(“CustomAttribute_TermDate”, oneDayMinus.toString()));
Filter ServiceAccount = Filter.ne(“employeeType”, “Service Account”);
Filter status = Filter.eq(“status”, “Terminated”);
Filter activeLinks = Filter.eq(“links.iiqDisabled”, false);
Filter terminatedUsers = Filter.or(terminationDate, status);
Filter filter = Filter.and(activeLinks, ServiceAccount, terminatedUsers );
QueryOptions queryOptions = new QueryOptions();
queryOptions.addFilter(filter);

But If I change terminatedUsers filter to .and then it is again return around 10000 users

2

Hi @Yaswanth24

You can log the filter using log.debug statement, this way you will come to know what is the filter that’s going against the tables. Same way you can perform select query on the tables to understand what data gap you are getting.

Also go through sailpoint java doc on filter usage and different methods present.

3

Hi @vinnysail

Thanks for suggesting. I have already logged the filter and it is as per requirement and it looks fine

4

Hi @Yaswanth24

were you able to understand why the number of identities that are getting returned is as per the requirement ?

5

try to replicate the same filter on Advanced Analytics → Advance Search step by step and check wich part of the filter broken the logic. I have some dobts on date.

6

@Yaswanth24 -

Below is a systematic way to understand why the new query is shrinking the result set and how to fix it.
Everything is SailPoint-specific, so I’m using “Identity” as the object class in the examples; adjust if you are actually querying Link, Bundle, etc.

1 Spot the two silent logic changes

# Old logic (12 k) New logic (5 k) Hidden side-effect
1 status = "Terminated" only status = "Terminated" OR (CustomAttribute_TermDate ≤ yesterday) OR is translated into SQL without parentheses, so precedence flips to:
(links … AND employeeType … AND termDate≤…) OR status='Terminated'
This returns only two groups:
• Identities that match all other predicates and have an old TermDate, plus
• Every identity whose status is “Terminated” (regardless of links/employeeType).
If most of your “Terminated” identities are also Service Accounts or have iiqDisabled = true, they get filtered away, shrinking the total.
2 no date maths you pass oneDayMinus.toString() (a 13-digit string) into Filter.le() le compares lexicographically when the attribute type is String. A date like 2025-05-17 T00:00:00 is greater than "1716048…", so the left-hand side rarely satisfies the predicate and collapses the OR branch.

Why Filter.and seemed “better”

When you changed

Filter terminatedUsers = Filter.or(termDate, status);

to

Filter terminatedUsers = Filter.and(termDate, status);

both branches now share the same attribute path, so the HQL generator keeps the parentheses and the query becomes

links … AND employeeType … AND (status='Terminated' AND termDate<=…)

That pulls more identities (≈10 k) because the date mismatch is the only blocker; you removed the precedence bug but still have the data-type bug.

2 Correct code (strict superset of the original)

// 1. Yesterday 00:00 (midnight) in one line
long millisInDay  = 24L * 60 * 60 * 1000;
Date  yesterday   = new Date(System.currentTimeMillis() - millisInDay);

// 2.  Termination date ≤ yesterday  AND  attribute is actually populated
Filter termDate = Filter.and(
        Filter.notnull("CustomAttribute_TermDate"),
        Filter.le      ("CustomAttribute_TermDate", yesterday)   // pass a Date, NOT a String
);

// 3.  Status == "Terminated"
Filter terminated = Filter.eq("status", "Terminated");

// 4.  Non-service accounts & still-enabled links
Filter nonService = Filter.ne("employeeType", "Service Account");
Filter active     = Filter.eq("links.iiqDisabled", Boolean.FALSE);

// 5.  Final filter:  active  AND  non-service  AND  (terminated  OR  termDate≤yesterday)
Filter finalFilter = Filter.and(active, nonService, Filter.or(terminated, termDate));

QueryOptions qo = new QueryOptions();
qo.addFilter(finalFilter);
// Optional but often useful:
qo.setDistinct(true);   // prevents “same identity shown multiple times” inflation

Why this fixes the count

  • Parentheses stay where you put them, because Filter.or( … ) is explicitly wrapped.
  • Data type matches: CustomAttribute_TermDate is compared to a java.util.Date, so the database does a true date comparison rather than a string comparison.
  • Count consistency:
    Everything that matched the old filter (status = “Terminated”) still matches, plus any identity whose TermDate is already in the past but whose status has not yet been flipped to “Terminated”.

You should now see ≥ 12 000 identities (never less).

Cheers!!!

7

Hi @sukanta_biswas

I have already tried you suggestions when I have used date object to filter identity in termdate filter it did not returned any users and with this Filter finalFilter = Filter.and(active, nonService, Filter.or(terminated, termDate));
the users returned were same around 5000,

for more info
this is final filter when returned.

<CompositeFilter operation="AND">
  <Filter operation="EQ" property="links.iiqDisabled">
    <Value>
      <Boolean></Boolean>
    </Value>
  </Filter>
  <Filter operation="NE" property="employeeType" value="Service Account"/>
  <CompositeFilter operation="OR">
    <CompositeFilter operation="AND">
      <Filter operation="NOTNULL" property="CustomAttribute_TermDate"/>
      <Filter operation="LE" property="CustomAttribute_TermDate">
        <Value>
          <Date>1747555430148</Date>
        </Value>
      </Filter>
    </CompositeFilter>
    <Filter operation="EQ" property="status" value="Terminated"/>
  </CompositeFilter>
</CompositeFilter>
8

Hi @vinnysail

when i verified the users who were returned they status is active with termination date in past wirth links active. This confirm that filter is working but I am not able to fetch all the users.

but the filter is not returing all the users who’s status is terminated

this is the filter when returned for more info

<CompositeFilter operation="AND">
  <Filter operation="EQ" property="links.iiqDisabled">
    <Value>
      <Boolean></Boolean>
    </Value>
  </Filter>
  <Filter operation="NE" property="employeeType" value="Service Account"/>
  <CompositeFilter operation="OR">
    <CompositeFilter operation="AND">
      <Filter operation="NOTNULL" property="CustomAttribute_TermDate"/>
      <Filter operation="LE" property="CustomAttribute_TermDate">
        <Value>
          <Date>1747555430148</Date>
        </Value>
      </Filter>
    </CompositeFilter>
    <Filter operation="EQ" property="status" value="Terminated"/>
  </CompositeFilter>
</CompositeFilter>
9

Hi @enistri_devo

When trying to replicate the filter in advance search

with termdate filter and status filter the users are same same around but when i add links.iiqDisabled == false the query returns none

(((CustomAttribute_TermDate != "null" && CustomAttribute_TermDate <= "1747551056924") && status == "Terminated") && employeeType != "Service Account" && links.iiqDisabled == false)