How to display cumulative results' count of events in timechart by hour?

[wencheng199999]

I want to show the sum of events in a search from the earliest time to the time increasing hour by hour. Because I want to see the sum of events changing with the time passing. How can i make it?

Features

1. calculate sum of events rom the earliest time to the time increasing hour by hour. For example: the query earliest time is -24 hours, and i need to know the sum of events in [-24,-23] hours, [-24,-22] hours and [-24,-21] hours...
2. There is a subsearch in the query.
3. The query for events is that the events are created but not started. And the events will be created or started at any time. So i need to calculate the sum from earliest time to a specified time, and make it a chart to see the change.

Now my code is:

index="index_kkk" category=Created earliest=-1d
| search NOT [search index="index_kkk" AND category=Started earliest=-1d 
                     | eval id=taskRequestId 
                     | fields id
             ]
| timechart count span=1h 
| streamstats sum(count) as cumulative

Anyone can help me?

[sundareshr]

Do you not get the desired results with your query? You should probably consider not using a sub-search for performance reasons. Like this

*UPDATED*

 index="index_xxx" (category=Created OR category="Started")  earliest=-1d
 | eval taskRequestId=coalesce(taskRequestId, id)
 | eventstats dc(category) as nbr_categories by taskRequestId 
 | where nbr_categories=1
 | timechart span=1h count

[wencheng199999]

@sundareshr Thanks. I've tried your query in splunk, but there is a error. There are some background features of this problem i need to tell you. So if you got it, i would appreaciated that you give me some advices to solve this problem!

---

Features

• A task has two category but shares only one taskId. And the task in splunk index has two events by different category that is either category=Created or category=Started.
• A task is created first and then started. Not every task is started just after created.
• So i need to i need to know the sum of tasks created but not started in [-24,-23], [-24,-22] and [-24,-21] ... [-24,now] hour.
• After i draw the chart, i can know the change trends of how many tasks created but not started.

• My query can just show the sum of tasks created but not started in [-24,now]hour. That troubled me.

  
index="index_kkk" category=Created earliest=-1d
| search NOT [search index="index_kkk" AND category=Started earliest=-1d
| fields taskId
]
| timechart count span=1h
| streamstats sum(count) as cumulative

  Is that clear for you? Could you give me some advices? Thank you very much!

[wencheng199999]

*@sundareshr Thanks. I've tried your query in splunk, the query get the created tasks number for each hour. But it didn't judge whether the task is started. The dc(category) is that you put to make it? but index="acadci_wk_prod" category=taskCreated make dc(category) always "1". So the query get the created tasks number for each hour.

I try to delete the "category=taskCreated", but i find that there is another trouble made by my own. The taskId named different at two events that it call "id" in created tasks,but "taskRequestId" in started tasks. Is that ok not changing the log script in the machine but still complete what i need? Thanks!
*

---

what i do:

• ```
  index="index_xxx" category=Created earliest=-1d
  | eval taskRequestId=id
  | eventstats dc(category) as nbr_categories by taskRequestId
  | where nbr_categories=1
  | timechart span=1h count

  ```
  It work in my splunk search, get the created tasks number for each hour. @sundareshr Thank you very much!

[sundareshr]

Since the ids are different (id vs taskRequestId), you will need to coalesce in to a single fieldname. Try the updated query. The dc(category) will updated each event with disctinct_count(category) by taskRequestId, we then exclude events where dc(Category)>1 which means, taskRequestId has more than one category (created, started)

[wencheng199999]

*@sundareshr Thanks. I've tried your query in splunk, the query get the results like before.
*

---

my query now:

• 
index="index_xxx" (category=Created OR category="Started") earliest=-1d
| eval taskRequestId=coalesce(taskRequestId, id)
| eventstats dc(category) as nbr_categories by taskRequestId
| where nbr_categories=1
| timechart span=1h count

  It works in my splunk search, gets the created tasks number in time range [-1d,now]. But not [-24,-23] hours, [-24,-22] hours and [-24,-21]...chart. Can the "timechart span=1h count" be changed to make what i need? @sundareshr Thank you very much!

[wencheng199999]

*@sundareshr Sorry. I think the "timechart span=1h count" only counts the task created and not started in a time range of [-k,-k+1]hour. so,it was not from -24houes. but a time span of a hour. Do you agree?
*

[sundareshr]

What was the error you get? Try the updated query.