topic Re: search a value in previous time period and add to current count in Splunk Search

search a value in previous time period and add to current count

avoelk — Tue, 07 Sep 2021 09:40:53 GMT

Hello!

is it possible to search a field value and then count it for example first the current week and then add the count of the same search from the week before ?

something like:

index=indexa action=allowed app=DNS dest="8.8.8.8" earliest=-14d latest=-7d
| eval flag="count1"
| append
[search index=indexa action=allowed app=DNS dest="8.8.8.8" earliest=-7d latest=now()
| eval flag="count2"]
| stats count(eval(flag="count1")) as count1 count(eval(flag="count2")) as count2
| eval count = count1+count2

Something in my use of the earliest/latest doesn't seem to work. what am I doing wrong?

Re: search a value in previous time period and add to current count

javiergn — Tue, 07 Sep 2021 10:06:31 GMT

Hi @avoelk ,

If you want to group by weeks then it might be easier to do something like:

index=indexa action=allowed app=DNS dest="8.8.8.8" earliest=-1w@w | bucket span=1w _time | stats count by _time | eventstats sum(count) as total_count

Or use timechart:

index=indexa action=allowed app=DNS dest="8.8.8.8" earliest=-1w@w | timechart span=1w count

Let me know if that works for you. Alternatively, if you want full weeks, then simply use:

earliest=-2w@w latest=@w

Re: search a value in previous time period and add to current count

avoelk — Tue, 07 Sep 2021 10:43:37 GMT

Hi @javiergn ,

thanks a lot. that worked too. is there a way to, if I'd have a field in which the maximum login count for a certain user resides, to add the amount of appearances this user logged in the week before?

so :

index=indexa action=allowed app=DNS logincount=* earliest=-7d latest=now()
| stats max(logincount) as max_count
| eval flag="count1"
| append
[search index=indexa action=allowed app=DNS logincount=* earliest=-14d latest=-7d
| stats count(if(max_count=logincount,1,0) as add_count
| eval flag="count2"]
| stats count(eval(flag="count1")) as count1 count(eval(flag="count2")) as count2
| eval count = count1+count2

but I know this doesn't work since the subsearch doesn't know what I mean with max_count=logincount. It's hard to explain .. in that case it should be like count the maximum login count within this week and show me the user, then look how often this user logged in the week before and add this amount to my current count.

Re: search a value in previous time period and add to current count

javiergn — Tue, 07 Sep 2021 11:47:48 GMT

Yes, kind of, the key is to do one thing at a time.

Your friend is eventstats in this case. So taking your description above and doing this step by step:

count the maximum login count within this week

| eventstats max(logincount) as max_count

and show me the user (assuming there is a field called user in this case):

| eval myuser = if(logincount = max_count, user, null())

then look how often this user logged in the week before

| bucket span=1w _time | eventstats count(eval(if(user=myuser,1,null()))) as myuser_count by _time

and add this amount to my current count.

This is the bit I don't get. Add it to which current count?

Re: search a value in previous time period and add to current count

avoelk — Tue, 07 Sep 2021 11:54:51 GMT

ah, eventstats!

so with eventstats I got a value of max logincount for example 10

after I've looked how often the user (who has the max logincount in the first search) logged in the past week , for example 5 times, I want to add this to the max logincount so 10+5 = 15

Re: search a value in previous time period and add to current count

javiergn — Tue, 07 Sep 2021 12:56:33 GMT

Something like the below perhaps (not tested obviously so there might be typos):

index=indexa action=allowed app=DNS dest="8.8.8.8" earliest=-2w@w latest=@w | bucket span=1w _time | eval mytime = if(_time=relative_time(now(), "-2w@w"), "last_week", "this_week") | eventstats max(logincount) as max_count by mytime | eval max_count_this_week=if(mytime="this_week", max_count, null()) | eventstats values(max_count_this_week) as max_count_this_week | eval user_this_week = if(logincount = max_count_this_week AND mytime="this_week", user, null()) | eventstats count by _time, user | eval final_count = if(user=user_this_week AND time="last_week", count+max_count_this_week, null()) | stats values(final_count) by user, mytime

Where you search across 2 weeks and create the variable mytime with two labels: "last_week" and "this_week" for readability.

Then calculate the max logincount this week and the user with that logincount, which then use to calculate the final count based on the user this week but the time period being last week.

The final stats is just to show you in tabular format.

Re: search a value in previous time period and add to current count

avoelk — Thu, 09 Sep 2021 12:57:18 GMT

Hi javiergn,

thanks for your help, there are some very useful parts in it. I had to adjust my question during the last few days so much that I created a new post here :

https://community.splunk.com/t5/Splunk-Search/Add-a-count-from-a-different-time-period/m-p/566452/thread-id/197406

that's the current "end goal" after I've talked with everyone again and figured out what the key points actually are.