Splunk Search

How to build query that Only shows new data compared to yesterday

winter0827
Engager

Hi all, I am trying to build a query that only shows the NEW results compared to yesterday.

I would like to get some alert and data to show ONLY if the message/key is new today, compared to the results yesterday.

for example: 

 

{query}
| stats count by key

 

Yesterday, the query returned - "key1", and "key2".

| key    | count |

| key1 | 10       |

| key2 | 5        |

Today, there are some results returned - "key1", and "key3". I would like to get the count of "key3" only as it is new today and didn't show up yesterday.

| key    | count |

| key3 | 15       |

Thanks in advance!

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
{query}
| stats count values(date) as date by key
| where mvcount(date) = 1 AND date = today

Obviously, you will need to adjust for your real data and field names

View solution in original post

0 Karma

marysan
Communicator

Assuming there is no "date" field in your log, first line, day field recognizes your day(today or yesterday or 2 days ago ...) , then second line specifies distinct days and count by each key and finally query shows unique keys related to today(day=1)
| eval day=round((now()-time)/86400,0)
| stats count,dc(day) as day_count by key
| where day_count =1 AND day=1 AND count=1

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
{query}
| stats count values(date) as date by key
| where mvcount(date) = 1 AND date = today

Obviously, you will need to adjust for your real data and field names

0 Karma

winter0827
Engager

Thanks, that solves my problem.

A follow up: how can I compare the values in last 2 weeks and only show the new ones in this week?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please clarify what you are trying to do - is it just a matter of setting the timeframe and using a span of 1 week instead of 1 day?

0 Karma

winter0827
Engager

firstly, I would like to compare day to day data. And list the count by key.

and then I want to expand the timeframe and compare the week by week, month by month data. 

for both cases above, I want to know which values are NEW in last day/week/month.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Bucket the time by the appropriate span and compare against the current bucket. Something like this

{query}
| bin _time as date span=1w
| eval today=relative_time(now(),"@w")
| stats count values(date) as date by key
| where mvcount(date) = 1 AND date = today
{query}
| bin _time as date span=1mon
| eval today=relative_time(now(),"@mon")
| stats count values(date) as date by key
| where mvcount(date) = 1 AND date = today

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...