Splunk Search

show unique values from yesterday

splunkapprentic
Explorer

Hello, please help!
I want to display only the unique names from yesterday that are not in today's list

Initial search:

Date Name Value
09/18/2016 ABC 2
09/18/2016 XYZ 9
09/18/2016 EFG 8
09/19/2016 ABC 3

Final search:

Date Name Value
09/18/2016 XYZ 9
09/18/2016 EFG 8

0 Karma
1 Solution

sundareshr
Legend

Try this

index=xyz  earliest=-1d@d | eval Date=strftime(_time, "%m/%d/%Y") | eval when=if(_time>relative_time(now(), "@d"), "Today", "Yesterday") | dedup Name Date | eval Value=Value."#".Date | chart values(Value) as Value over Name by when | where isnull(Today) AND isnotnuill(Yesterday) | table Name Yesterday | rex field=Value "(?<Value>[^\#]+)\#(?<Date>.*)" | table Date Name Value

*UPDATED*

BASE SEARCH | eval when=if(strptime(Date, "%m/%d/%Y")>relative_time(now(), "@d"), "Today", "Yesterday") | dedup Name Date | eval Value=Value."#".Date | chart values(Value) as Value over Name by when | where isnull(Today) AND isnotnuill(Yesterday) | table Name Yesterday | rex field=Value "(?[^\#]+)\#(?.*)" | table Date Name Value

OR THIS*

base search | sort - Date | dedup Name | where strptime(Date, "%m/%d/%Y")<relative_time(now(), "@d")) 

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Assuming that you've a working search giving you your initial output, try this

your current search giving fields  Date Name Value | stats Values(Date) as Date values(Value) as Value by Name | where mvcount(Date)=1 AND strptime(Date, "%m/%d/%Y")>relative_time(now(), "@d")
0 Karma

sundareshr
Legend

Try this

index=xyz  earliest=-1d@d | eval Date=strftime(_time, "%m/%d/%Y") | eval when=if(_time>relative_time(now(), "@d"), "Today", "Yesterday") | dedup Name Date | eval Value=Value."#".Date | chart values(Value) as Value over Name by when | where isnull(Today) AND isnotnuill(Yesterday) | table Name Yesterday | rex field=Value "(?<Value>[^\#]+)\#(?<Date>.*)" | table Date Name Value

*UPDATED*

BASE SEARCH | eval when=if(strptime(Date, "%m/%d/%Y")>relative_time(now(), "@d"), "Today", "Yesterday") | dedup Name Date | eval Value=Value."#".Date | chart values(Value) as Value over Name by when | where isnull(Today) AND isnotnuill(Yesterday) | table Name Yesterday | rex field=Value "(?[^\#]+)\#(?.*)" | table Date Name Value

OR THIS*

base search | sort - Date | dedup Name | where strptime(Date, "%m/%d/%Y")<relative_time(now(), "@d")) 
0 Karma

splunkapprentic
Explorer

I don't think I have the _time records 😞

0 Karma

splunkapprentic
Explorer

yeah! the last one seems to do the job 😉

0 Karma

sundareshr
Legend

@splunkapprentice please accept this answer to close it out.

0 Karma

sundareshr
Legend

Try the updated query

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...