Splunk Search
Highlighted

How to remove common field values after join?

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
Highlighted

Re: How to remove common field values after join?

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, an upvote would be appreciated.
0 Karma