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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...