How to build timechart (?conditional)

[rus7am]

Hi guys,

I have a ticket history collected from our system:
TicketNumber,State,OpenDate (od) , ClosureDate (cd)
1,OPEN,od1,-
2,OPEN,od2,-
3,CLOSED,od3,cd3
4,OPEN,od4,-
5,CLOSED,od5,cd5

I need to build a trend line with following criteria:

X axis: date item (for example 7th of July).
Y axis: count of tickets which was opened before this time ( OD <= TimeItem ) and closed after this time ( CD > TimeItem). How many tickets was opened and not closed for this time.

I've built that in excel:
Column of Dates | =COUNTIFS(OpenDateColumnItem,"<=" & DateItem,ClosureDateColumnItem">" & DateItem)

Merry Christmas and Happy Holidays!

[martin_mueller]

Give this a shot:

... | eval duration = ClosureDate - OpenDate | concurrency duration=duration | timechart max(concurrency) as OpenTickets

I've assumed the dates are in epoch, if not you'll have to strptime() them first.

[martin_mueller]

Got more details than "not successful"?

[rus7am]

Hello Martin,

Let me try to explain. Excel formulas works well, here is data after calculations to build the graph:

Date TicketTrend

18.09.2014 2464
23.09.2014 2175
28.09.2014 2095
03.10.2014 2616
08.10.2014 2887
13.10.2014 2769
18.10.2014 3115
23.10.2014 3292
28.10.2014 3983
02.11.2014 6458

If i'm not mistaken Statistics tab in Splunk should have similar data, but:

_time OpenTickets
2014-11-28 110
2014-11-29 0
2014-11-30 0
2014-12-01 111
2014-12-02 0
2014-12-03 115
2014-12-04 116
2014-12-05 0
2014-12-06 0
2014-12-07 0

[rus7am]

Hello, thanks for quick response!

I've used following search:

index=index_system sourcetype=system_tickets | dedup TicketNumber | eval OpenDate=strptime(OpenDate,"%Y-%m-%d") | replace - with "2014-12-27" in ClosureDate | eval ClosureDate=strptime(ClosureDate,"%Y-%m-%d") | eval duration = ClosureDate - OpenDate | concurrency duration=duration | timechart max(concurrency) as OpenTickets

It was not successful.