https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169436#M48415 <P>Got more details than "not successful"?</P> Fri, 26 Dec 2014 17:31:38 GMT martin_mueller 2014-12-26T17:31:38Z https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169433#M48412 <P>Hi guys,</P> <P>I have a ticket history collected from our system:<BR /> TicketNumber,State,OpenDate (od) , ClosureDate (cd)<BR /> 1,OPEN,od1,-<BR /> 2,OPEN,od2,-<BR /> 3,CLOSED,od3,cd3<BR /> 4,OPEN,od4,-<BR /> 5,CLOSED,od5,cd5</P> <P>I need to build a trend line with following criteria:</P> <P>X axis: date item (for example 7th of July). <BR /> Y axis: count of tickets which was opened before this time ( OD &lt;= TimeItem ) and closed after this time ( CD &gt; TimeItem). How many tickets was opened and not closed for this time.</P> <P>I've built that in excel:<BR /> Column of Dates | =COUNTIFS(OpenDateColumnItem,"&lt;=" &amp; DateItem,ClosureDateColumnItem"&gt;" &amp; DateItem)</P> <P>Merry Christmas and Happy Holidays!</P> Thu, 25 Dec 2014 10:19:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169433#M48412 rus7am 2014-12-25T10:19:37Z https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169434#M48413 <P>Give this a shot:</P> <PRE><CODE>... | eval duration = ClosureDate - OpenDate | concurrency duration=duration | timechart max(concurrency) as OpenTickets </CODE></PRE> <P>I've assumed the dates are in epoch, if not you'll have to <CODE>strptime()</CODE> them first.</P> Thu, 25 Dec 2014 13:26:24 GMT https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169434#M48413 martin_mueller 2014-12-25T13:26:24Z https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169435#M48414 <P>Hello, thanks for quick response!</P> <P>I've used following search:</P> <P>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</P> <P>It was not successful.</P> <P><IMG src="https://dc09ca52-a-62cb3a1a-s-sites.googlegroups.com/site/rabdullinfiles/splunk/Splunk%20Graph.png" alt="alt text" /></P> Mon, 28 Sep 2020 18:32:49 GMT https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169435#M48414 rus7am 2020-09-28T18:32:49Z https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169436#M48415 <P>Got more details than "not successful"?</P> Fri, 26 Dec 2014 17:31:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169436#M48415 martin_mueller 2014-12-26T17:31:38Z https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169437#M48416 <P>Hello Martin,</P> <P>Let me try to explain. Excel formulas works well, here is data after calculations to build the graph:</P> <P>Date TicketTrend</P> <P>18.09.2014 2464<BR /> 23.09.2014 2175<BR /> 28.09.2014 2095<BR /> 03.10.2014 2616<BR /> 08.10.2014 2887<BR /> 13.10.2014 2769<BR /> 18.10.2014 3115<BR /> 23.10.2014 3292<BR /> 28.10.2014 3983<BR /> 02.11.2014 6458</P> <P>If i'm not mistaken Statistics tab in Splunk should have similar data, but:</P> <P>_time OpenTickets<BR /> 2014-11-28 110<BR /> 2014-11-29 0<BR /> 2014-11-30 0<BR /> 2014-12-01 111<BR /> 2014-12-02 0<BR /> 2014-12-03 115<BR /> 2014-12-04 116<BR /> 2014-12-05 0<BR /> 2014-12-06 0<BR /> 2014-12-07 0</P> Sun, 11 Jan 2015 10:23:05 GMT https://community.splunk.com/t5/Splunk-Search/How-to-build-timechart-conditional/m-p/169437#M48416 rus7am 2015-01-11T10:23:05Z