Splunk Search

How to build timechart (?conditional)

Explorer

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!

0 Karma

SplunkTrust
SplunkTrust

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.

SplunkTrust
SplunkTrust

Got more details than "not successful"?

0 Karma

Explorer

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

0 Karma

Explorer

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.

alt text

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!