topic Re: Add a count from a different time period in Splunk Search

Add a count from a different time period

avoelk — Thu, 09 Sep 2021 12:53:00 GMT

Hello,

I'm trying to add the appearance of a certain value in my base search count. the value is "detatched". it is written in an event, when a certain license has been used. this detatched license has a lifespan of 14 days, afterwards it's not active anymore and I don't need to add this to my base search anymore.

so basically it's like this :

index=indexa=* licensecount=* productid=5000 earliest=-30d@d latest=now()
| eval flag="basecount"
| append
[search index=indexa =* productid=5000 subject="*detatched*" earliest=-45d@d latest=-31d@d
| eval flag="addcount"]
| stats count(eval(flag="basecount")) as basecount count(eval(flag="addcount")) as addcount
| eval totalcount = basecount+addcount
|timechart span=1d count(totalcount)

I know this query is partlially stupid but what I want to show is what I'm trying to accomplish. Example: Today I have a licence count of the product 5000 of 5, 14 days ago I had a count of 1, therefore today it should show me 6. tomorrow, this count of 1 shouldn't be added anymore, cause it's more than 14 days old and not active anymore. this should be seen - ideally - in a timechart.

Hope someone can make sense of this . Much appreciate any help or feedback, cause, maybe it's not possible to do so in splunk.

Thanks a lot guys

Re: Add a count from a different time period

javiergn — Thu, 09 Sep 2021 13:36:04 GMT

Hi again @avoelk,

So resuming where we left it here: https://community.splunk.com/t5/Splunk-Search/search-a-value-in-previous-time-period-and-add-to-current-count/m-p/566103, I would try the following (keep in mind there might be typos as I did not test this on any lab):

index=indexa licensecount=* earliest=-45d@d latest=now() | stats count(eval(if(_time >= relative_time(now(), "-30d@d"), 1, null())))) as basecount, count(eval(if(_time >= relative_time(now(), "-45d@d") AND _time < relative_time(now(), "-30d@d") AND match(subject, "detatched"), 1, null())))) as addcount by productid | eval totalcount = basecount + addcount | timechart span=1d count(totalcount) by productid

Re: Add a count from a different time period

avoelk — Tue, 05 Oct 2021 14:37:44 GMT

hello and thanks a lot for the continuous help,

I'm going to test this query tomorrow. it looks like the right thing we need. will keep you updated and thanks again!

Re: Add a count from a different time period

avoelk — Thu, 07 Oct 2021 08:25:59 GMT

Hello and thank you for your help,

after further discussion I think the best way of putting it is the following:

1.)

I need a maximum of a value of a certain attribute at a specific day as basecount and then looks back 14 days (related to this specific day) and counts the occurance of events which contain the word "detatched" and add this as a count to the basecount.

2.)

this would be the view for a specific day. after this I'd need this view, but for a timewindow of for example 7 days (sliding timewindow).

It's the best way of finally putting it. I hope you get what I mean. 🙂 I'm sorry that I switched back and forth with the explanation but as you can see it's not an easy way of describing it.

Thanks a lot for the help!