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

[avoelk]

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? 

[javiergn]

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

[avoelk]

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. 

[javiergn]

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?

[avoelk]

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

[javiergn]

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.

[avoelk]

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/th...

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