Splunk Search

How to remove common field values after join?

JoshuaJohn
Contributor

I have two indexes. I can join them and see the results based on a common field. I want to see only the results in the second index that are not part of the first index.

index=BASE earliest=0
| eval LPR = strptime(LastPolicyRequest, "%m/%d/%Y %I:%M:%S %p") 
| where LPR >= relative_time(now(),"-7d@h")
| table "WiFiMAC","LastPolicyRequest","ValidFrom","ValidTo" 
| join type=left WiFiMAC 
    [ search BASE earliest =-48h
    | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") 
    | eval Indextime =strptime(indextime,"%Y-%m-%d %H:%M:%S.%N") 
    | eval Time =strptime(_time,"%s") 
    | eval Minutes_Diff = round((Indextime - Time)/60,2) 
    | stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by WiFiMAC 
    | eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
    | rename count as "Sample Size" 
    | table "WiFiMAC", "Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff", "Sample Size" ] 
| table "WiFiMAC", "Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff","ValidFrom","ValidTo","LastPolicyRequest", "Sample Size"
| dedup WiFiMAC

Any Ideas?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You've asked two different questions. To remove fields after a join or any other command, use fields - followed by the names of the fields you want to suppress.

To see only the results in the second index that are not part of the first index, try a subsearch.

index=BASE earliest =-48h NOT [index=BASE earliest=0 | eval LPR = strptime(LastPolicyRequest, "%m/%d/%Y %I:%M:%S %p") | where LPR >= relative_time(now(),"-7d@h")] 
| eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") 
| eval Indextime =strptime(indextime,"%Y-%m-%d %H:%M:%S.%N") 
| eval Time =strptime(_time,"%s") 
| eval Minutes_Diff = round((Indextime - Time)/60,2) 
| stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by WiFiMAC 
| eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
| rename count as "Sample Size"
| dedup WiFiMAC
| table "WiFiMAC", "Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff","ValidFrom","ValidTo","LastPolicyRequest", "Sample Size"
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...

Splunk Observability for AI

Don’t miss out on an exciting Tech Talk on Splunk Observability for AI!Discover how Splunk’s agentic AI ...

🔐 Trust at Every Hop: How mTLS in Splunk Enterprise 10.0 Makes Security Simpler

From Idea to Implementation: Why Splunk Built mTLS into Splunk Enterprise 10.0  mTLS wasn’t just a checkbox ...