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

[winter0827]

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!

[marysan]

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

[ITWhisperer · Accepted solution]

{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

[winter0827]

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?

[ITWhisperer]

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?

[winter0827]

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.

[ITWhisperer]

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