pivot | where

[kensternberg]

I want to use a 'where' clause (which allows the comparison of two fields) as a pivot constraint. My original search is

index=maillog (event=SEND OR event=RECEIVE)

Which gives me all the successful mail handling events. The input source breaks out the root domain of the sender and receiver into individual fields, and I want to be able to say

| where sender_domain=receiver_domain

In a standard search, this was easy. I'd like to use this in pivot, but I can't figure out how to make the 'where' clause a child constraint of the main search, so I can say "show me the maillog of internal communications."

Help, please?

[kensternberg]

I tried that. The constraint always wants to treat the right side of the expression as a string, not a field name. The eval method described in the answer I posted was much more effective.

[somesoni2]

But you will not need to specify 'where' keyword. You can just specify the condition (" sender_domain=receiver_domain") directly in the "Additional constraints" text area.

[kensternberg]

@somesoni2: The problem is that the 'where' clause isn't a legal operator in the constraints object.

[kensternberg · Accepted solution]

It turns out you can't create a where clause in Pivot.

But, you can create a calculated field. In the Data Model Editor, Add Attribute -> Eval Expression. I used the field name is_internal_communication, and an evaluation

if (sender_domain = receiver_domain, 1, 0)  

After previewing and saving, I was able to create a new child object with the constraint is_internal_communication = 1, and obviously I could add a child object with the constraint in_internal_communication = 0 for external communications.

[somesoni2]

You can create a Child to your data model and add Child object. In Child object you can specify your where clause as "Additional Constraints". Is that you're looking for?