where clause evaluates differently with presence of fields in prior table command

[sdesigowda]

I am putting a query to findout all SSH connection between internal network and external network. Logic I am trying is very straight forward. I lookup src and dest ip fields in a lookup table. If both src and dest are present in the lookup table then it'[s internal SSH connection else it's external connection.

query
sourcetype="cefevents" | join MyFlowID max=0 [ search MyApplicationName=ssh ] | transaction MyFlowID src dst | dedup src dst | lookup corp_networks network_prefix as src output valid as srcpresent | lookup corp_networks network_prefix as dst output valid as dstpresent | lookup host_addr_to_name host_addr as src output host_name as "Src Name" | lookup host_addr_to_name host_addr as dst output host_name as "Dst Name" | iplocation src | rename Country as "Src Country" | iplocation dst | rename Country as "Dst Country" | rename spt as "Src Port" dpt as "Dst Port" | table src "Src Name" "Src Country" dst "Dst Name" "Dst Country" "Src Port" "Dst Port" MyApplicationName MyFlowID | where NOT (dstpresent == 1 and srcpresent ==1)

Output
src Src Name Src Country dst Dst Name Dst Country Src Port Dst Port MyApplicationName MyFlowID
40.40.40.19 50.1.10.2 XXXXX 22 51270 ssh 505345970676954420
40.40.40.40 50.1.10.2 XXXXX 22 52427 ssh 649486050193769777

Query with srcpresent and dstpresent in table command
strong text

sourcetype="cefevents" | join MyFlowID max=0 [ search MyApplicationName=ssh ] | transaction MyFlowID src dst | dedup src dst | lookup corp_networks network_prefix as src output valid as srcpresent | lookup corp_networks network_prefix as dst output valid as dstpresent | lookup host_addr_to_name host_addr as src output host_name as "Src Name" | lookup host_addr_to_name host_addr as dst output host_name as "Dst Name" | iplocation src | rename Country as "Src Country" | iplocation dst | rename Country as "Dst Country" | rename spt as "Src Port" dpt as "Dst Port" | table srcpresent dstpresent src "Src Name" "Src Country" dst "Dst Name" "Dst Country" "Src Port" "Dst Port" MyApplicationName MyFlowID | where NOT (dstpresent == 1 and srcpresent ==1)

output:
empty as expected...

How do I achieve the expected output without putting srcpresent dstpresent in the table command prior to where condition.
Table is acting as a filter to eliminate fields.

[sdesigowda]

thanks!!! moving "where" clause earlier to table works

[acharlieh]

Move your where clause to before your table clause.

You are correct that table is acting as a filter to eliminate fields, much like where is acting as a filter to eliminate rows. The problem is that your where clause is using fields that were eliminated by your table clause... so either, you need to include those fields in your table, or you move your where clause to be earlier in your search.

[DalJeanis]

I suspect it's not really the where clause that's doing it. It's optimization.

Try adding this on the end of the search version without the table.

   | noop search_optimization=false

You can also try adding ...

 | rename  srcpresent as  srcpresent1, dstpresent as dstpresent1

... where the table currently is and see if that fixes the issue.

[sdesigowda]

Both options not working 😞