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?
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"