Splunk Search

How to build timechart (?conditional)

rus7am
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

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

martin_mueller
SplunkTrust
SplunkTrust

Got more details than "not successful"?

0 Karma

rus7am
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

rus7am
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
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...