Splunk Search
Highlighted

Historical count of open Jobs - at point in time.

Engager

I have a simple ticketing system.

I need to show the number of tickets open for each client at the end of each week - since 1/1/2015.

This search will show the number of jobs opened for that week

...Project="MyClient1" 
| eval DateCreated=strptime(Created,"%d/%m/%Y %H:%M")
| eval DateResolved=strptime(Resolved,"%d/%m/%Y %H:%M")
| eval JobState=if(DateResolved>DateCreated,"Calculated Closed","CalculatedAsOpen")
| where JobState="CalculatedAsOpen"
| timechart span=1week count(JobState) by JobState

but does not count jobs open from previous weeks.

The query would need to evaluate DateResolved against the current timechart time to determine if it was still open.

A similar question is posted here https://answers.splunk.com/answers/78275/timechart-accumulation-of-all-events-from-previous-times.ht...

AJ

alt text

0 Karma
Highlighted

Re: Historical count of open Jobs - at point in time.

Splunk Employee
Splunk Employee

Without seeing your dataset, I can recommend that you would change your query a bit..

 ... | bin span=1w _time | stats count(JobState) by Customer

This assumes that you have a value for Customer declared, and that each event has this Customer field in the data.

0 Karma
Highlighted

Re: Historical count of open Jobs - at point in time.

Engager

My example search is probably misleading.

The JobState needs to be evaluated against the "point in time the TimeChart/Bin is currently in" to determine if the Job was open at that point in time.

Eg If DateResolved > CurrentDateInTimeChart then JobState=Open

This same problem is presented (and better articulated) here:
https://answers.splunk.com/answers/78275/timechart-accumulation-of-all-events-from-previous-times.ht...

0 Karma