topic Re: pivot | where in Reporting

pivot | where

kensternberg — Wed, 14 May 2014 16:46:04 GMT

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?

Re: pivot | where

somesoni2 — Wed, 14 May 2014 18:26:11 GMT

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?

Re: pivot | where

kensternberg — Wed, 14 May 2014 18:57:19 GMT

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.

Re: pivot | where

kensternberg — Wed, 14 May 2014 19:33:35 GMT

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

Re: pivot | where

somesoni2 — Mon, 28 Sep 2020 16:36:57 GMT

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.

Re: pivot | where

kensternberg — Wed, 14 May 2014 20:23:24 GMT

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.