Reporting

pivot | where

kensternberg
Splunk Employee
Splunk Employee

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?

Tags (2)
0 Karma
1 Solution

kensternberg
Splunk Employee
Splunk Employee

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.

View solution in original post

kensternberg
Splunk Employee
Splunk Employee

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.

0 Karma

somesoni2
Revered Legend

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.

0 Karma

kensternberg
Splunk Employee
Splunk Employee

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

0 Karma

kensternberg
Splunk Employee
Splunk Employee

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
Revered Legend

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?

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...