topic How to build query that Only shows new data compared to yesterday in Splunk Search

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

winter0827 — Mon, 13 Jun 2022 22:48:52 GMT

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!

Re: Only show new data compared to yesterday

ITWhisperer — Tue, 07 Jun 2022 05:25:31 GMT

{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

Re: Only show new data compared to yesterday

winter0827 — Mon, 13 Jun 2022 22:48:08 GMT

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?

Re: Only show new data compared to yesterday

ITWhisperer — Tue, 14 Jun 2022 05:23:01 GMT

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?

Re: Only show new data compared to yesterday

winter0827 — Tue, 14 Jun 2022 08:10:04 GMT

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.

Re: Only show new data compared to yesterday

ITWhisperer — Tue, 14 Jun 2022 08:18:55 GMT

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

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

marysan — Tue, 14 Jun 2022 08:26:02 GMT

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