topic Re: Getting the occurrence count when there is no event for the same in Splunk Search

Getting the occurrence count when there is no event for the same

av_ — Mon, 20 Sep 2021 06:10:58 GMT

This is the dummy dataset which has been created to address the issue I am facing.
I want to count the number of occurrences of the task with respect to its state per day three times.
I have tried using timechart span=3h count by state in my query but I am unable to count the state when there is no event present for the same.

The output expected is :

Kindly help!!!

Re: Getting the occurrence count when there is no event for the same

PickleRick — Mon, 20 Sep 2021 06:28:16 GMT

I don't understand. timechart count by Status should show you number of events per span period or 0 if no events with given Status were present. And it works so in my case.

Re: Getting the occurrence count when there is no event for the same

av_ — Mon, 20 Sep 2021 06:35:30 GMT

Exactly!!! Timechart would show 0 if no events with given Status were present but I want the count of the state till the time the task is in a particular state.

e.g. Num123 is in Draft state from 3rd Sep to 10 Sep. So the requirement if to count the task as 1 from 3rd Sep to 10 Sep in Draft. The logic behind this is that even though we have got the events only thrice (for Draft state) in Splunk but the task is continuously present in Draft state throughout beginning from 3rd Sep till 10the Sep.

Re: Getting the occurrence count when there is no event for the same

PickleRick — Mon, 20 Sep 2021 06:51:22 GMT

Ahhhh... so you want to "stretch" the occurences - to the dates between those that you have in readings?

Re: Getting the occurrence count when there is no event for the same

av_ — Mon, 20 Sep 2021 06:54:18 GMT

Yes, you got me now 🙂
Please help me with this.

Re: Getting the occurrence count when there is no event for the same

PickleRick — Mon, 20 Sep 2021 08:03:31 GMT

It ain't easy. And it will probably need some tweaking to account for the time near the transition between states but in general you need to find the start and end time for each state, then generate all needed times "in between" and fill the gaps. Something like this:

Re: Getting the occurrence count when there is no event for the same

ITWhisperer — Mon, 20 Sep 2021 09:27:02 GMT

The part before the blank lines sets up some dummy data

Re: Getting the occurrence count when there is no event for the same

av_ — Tue, 21 Sep 2021 05:33:34 GMT

Hi!!!
Your solution works fine for one task but in the real dataset I have more than a hundred tasks. How to evaluate the count in such case?

Re: Getting the occurrence count when there is no event for the same

ITWhisperer — Tue, 21 Sep 2021 08:05:50 GMT

Try something like this

Re: Getting the occurrence count when there is no event for the same

av_ — Tue, 21 Sep 2021 12:56:04 GMT

Thank you 🙂

This worked for me.

Re: Getting the occurrence count when there is no event for the same

av_ — Wed, 29 Sep 2021 16:00:26 GMT

Hi @ITWhisperer ,

The solution given by you is working perfectly fine but I am facing one issue with one scenario.

Let' say I have selected the time range as "All Time" and there is a task which is in Draft from 19-Feb-21 to 20-Feb-21, WIP from 21-Feb-21 to 23-Feb-21 and the task gets completed on 24-Feb-21. Since, we have used "Filldown" in the query, it is filling all the timestamps with "Complete" till today which is wrong as we want only one entry for "Complete".

PS: the "Filldown" is working fine for Draft & WIP. Since "Complete" is the last stage, so only one entry should be visible. Is there any way to do that?

Re: Getting the occurrence count when there is no event for the same

ITWhisperer — Wed, 29 Sep 2021 17:43:18 GMT

Re: Getting the occurrence count when there is no event for the same

av_ — Thu, 30 Sep 2021 05:39:51 GMT

This one's not working 😞

Re: Getting the occurrence count when there is no event for the same

av_ — Thu, 30 Sep 2021 06:13:34 GMT

"Task123" is completed on 15-Mar-21 but the entry is getting extended till 04-Jun-21, that's because the other task "Task456" is getting completed on 04-Jun-21. So the requirement is that the Completed entry for "task123" shall only be restricted till 15-Mar-21.

The entries marked in Yellow are not required.

Re: Getting the occurrence count when there is no event for the same

ITWhisperer — Thu, 30 Sep 2021 07:33:43 GMT

Which version of splunk are you using?

Can you post your complete search?

My guess is that the untable isn't joining the multi-value field in the same way as the version I am using - you might need to add an mvjoin in there

Re: Getting the occurrence count when there is no event for the same

av_ — Thu, 30 Sep 2021 09:13:01 GMT

I'm working on Splunk version 8.2

sourcetype="pqr:task" dv_number="task*"
| table dv_number dv_state _time

| stats earliest(_time) as _time by dv_number dv_state

| eval _time=relative_time(_time,"@d")

| xyseries _time dv_number dv_state

| makecontinuous _time span=1d

| filldown

| streamstats values(*) as * window=2 current=t

| untable _time dv_number dv_state

| eval dv_state=if(dv_state="complete",null(),mvindex(split(dv_state," "),0))

| eval count=1

| eval _time=_time."!".dv_number

| xyseries _time dv_state count

| fillnull value=0

| eval number=mvindex(split(_time,"!"),1)

| eval _time=mvindex(split(_time,"!"),0)

| table _time number Draft "Work in progress" Complete

| stats sum(Draft) as Draft, sum(Work in progress) as "WIP", sum(Complete) as Complete by _time

Re: Getting the occurrence count when there is no event for the same

ITWhisperer — Thu, 30 Sep 2021 09:16:11 GMT

I used lower case in my example - you should change this line

| eval dv_state=if(dv_state="Complete",null(),mvindex(split(dv_state," "),0))

Re: Getting the occurrence count when there is no event for the same

av_ — Thu, 30 Sep 2021 10:44:02 GMT

oh my bad!!! It's working....Thanks a ton 🙂

Re: Getting the occurrence count when there is no event for the same

av_ — Tue, 12 Oct 2021 04:06:25 GMT

Hi @ITWhisperer ,

There has been an enhancement in my use case. Would need your help!!!

Now, we need to take Assignment Group(AG) into consideration as well. If a task is in Draft state and it belongs to "AG1" & after some days it gets assigned to "AG2", so we need to count the transition in state with respect to the assignment group it belongs to.

We want to calculate for how many days task123 was in a particular state & belonged to AG1, then it got assigned to AG2 so for how many days it was in a particular state with AG2 as assignment group.