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

[av_]

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!!!

[ITWhisperer]

| gentimes start=-14
| streamstats count as row
| eval number="num123"
| eval state=case(row<8,"draft",row<14,"wip",true(),"complete")
| eval _time=if(row=8,starttime-(24*60*60)+60,if(row=14,starttime-(24*60*60)+60,starttime))
| where row%3!=0 AND row%5!=0
| table number state _time



| stats earliest(_time) as _time by number state
| sort _time
| eval _time=relative_time(_time,"@d") 
| makecontinuous _time span=1d
| filldown number state
| eval count=1
| xyseries _time state count
| fillnull value=0
| table _time draft wip complete

The part before the blank lines sets up some dummy data

[av_]

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?

[ITWhisperer · Accepted solution]

Try something like this

| gentimes start=-14
| streamstats count as row
| eval number="num123"
| eval state=case(row<8,"draft",row<14,"wip",true(),"complete")
| eval _time=if(row=8,starttime-(24*60*60)+60,if(row=14,starttime-(24*60*60)+60,starttime))
| where row%3!=0 AND row%5!=0
| table number state _time
| append 
    [| gentimes start=-20
    | where starttime < relative_time(now(),"-6d")
| streamstats count as row
| eval number="num456"
| eval state=case(row<8,"draft",row<14,"wip",true(),"complete")
| eval _time=if(row=8,starttime-(24*60*60)+60,if(row=14,starttime-(24*60*60)+60,starttime))
| where row%3!=0 AND row%5!=0
| table number state _time
]


| stats earliest(_time) as _time by number state
| eval _time=relative_time(_time,"@d")
| xyseries _time number state 
| makecontinuous _time span=1d
| filldown
| untable _time number state
| eval count=1
| eval _time=_time."!".number
| xyseries _time state count
| fillnull value=0
| eval number=mvindex(split(_time,"!"),1)
| eval _time=mvindex(split(_time,"!"),0)
| table _time number draft wip complete

[av_]

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?

[ITWhisperer]

| gentimes start=-14
| streamstats count as row
| eval number="num123"
| eval state=case(row<8,"draft",row<14,"wip",true(),"complete")
| eval _time=if(row=8,starttime-(24*60*60)+60,if(row=14,starttime-(24*60*60)+60,starttime))
| where row%3!=0 AND row%5!=0
| table number state _time
| append 
    [| gentimes start=-20
    | where starttime < relative_time(now(),"-6d")
| streamstats count as row
| eval number="num456"
| eval state=case(row<8,"draft",row<14,"wip",true(),"complete")
| eval _time=if(row=8,starttime-(24*60*60)+60,if(row=14,starttime-(24*60*60)+60,starttime))
| where row%3!=0 AND row%5!=0
| table number state _time
]


| stats earliest(_time) as _time by number state
| eval _time=relative_time(_time,"@d")
| xyseries _time number state 
| makecontinuous _time span=1d
| filldown
| streamstats values(*) as * window=2 current=t
| untable _time number state
| eval state=if(state="complete",null(),mvindex(split(state," "),0))
| eval count=1
| eval _time=_time."!".number
| xyseries _time state count
| fillnull value=0
| eval number=mvindex(split(_time,"!"),1)
| eval _time=mvindex(split(_time,"!"),0)
| table _time number draft wip complete

[av_]

"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.

[av_]

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.

[ITWhisperer]

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

[av_]

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

Reply

[av_]

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

[ITWhisperer]

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))

[av_]

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

[av_]

This one's not working 😞

[av_]

Thank you 🙂

This worked for me.

[PickleRick]

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

[av_]

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

[PickleRick]

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:

| streamstats earliest(_time) as mintime by State reset_on_change=true
| stats max(_time) as maxtime values(State) by mintime 
| eval maxtime=if(mintime=maxtime,mintime+3*3600,maxtime) 
| eval times=mvrange(mintime,maxtime,3600)
| mvexpand times 
| fields - maxtime mintime
| rename times as _time

[PickleRick]

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.

[av_]

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.