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!
{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
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
{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
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?
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?
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.
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