https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220802#M64877 <P>Lets try a different approach</P> <PRE><CODE>index=dynatrace "failed events" transactioname=* | stats earliest(_time) as start latest(_time) as end by host transactionname | eval duration=tostring(round(end-start, 0), "duration") </CODE></PRE> Fri, 12 Aug 2016 18:38:35 GMT sundareshr 2016-08-12T18:38:35Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220792#M64867 <P>Hello Splunk'all,</P> <P>I am trying to derive a simple chart from the data I got here within a Splunk Index. The data consists of epoch time (field name is "<STRONG>transactiontime</STRONG>") and the transaction values. The transactions contains success and failures and every transaction has an epoch timestamp for the transaction occurred.<BR /> I am simply trying to create a graph with the data for the time between every failure. For example: in a day, if there are two failures, one at 3 AM and one at 8 PM, I am trying to create a graph and show the time between these two failures (which is 17 Hrs). SO I took some help from a colleague of mine and got to this stage (please see below) and I am able to take two transactions into one field, but the time difference between those epoch values of two transactions are showing wrong. <BR /> Please see the search below and let me know for any missing logic I need to incorporate here:</P> <PRE><CODE>index="dynatrace" transactionname="*" sort _time| search tpf &gt; 0 | eval cvtime=strftime(transactiontime/1000, "%H:%M:%S %d-%m-%Y") | transaction host maxevents=2 | eval mdiff=round(duration)| table mdiff,cvtime,ttime </CODE></PRE> <P>Thanks <BR /> Vikram Y</P> Tue, 09 Aug 2016 21:17:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220792#M64867 vikramyerneni 2016-08-09T21:17:41Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220793#M64868 <P>Try this</P> <P>*<STRONG><EM>UPDATED</EM></STRONG>*</P> <PRE><CODE> index=dynatrace "failed events" transactioname=* | stats earliest(_time) as start latest(_time) as end by host transactionname | eval duration=tostring(round(end-start, 0), "duration") </CODE></PRE> Tue, 09 Aug 2016 22:23:31 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220793#M64868 sundareshr 2016-08-09T22:23:31Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220794#M64869 <P>Hi sundar,<BR /> I do see your logic here. You took the "_time" and calculated the output based on the parameter we need ("<STRONG>failed events</STRONG>"in this case). I am getting the full list individually now in the output. Thats really good..<BR /> I am actually trying to achieve to get the time difference between two individual events and put them into the Visualization in Splunk.</P> <P>Thanks<BR /> Vikram Y</P> Wed, 10 Aug 2016 15:14:00 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220794#M64869 vikramyerneni 2016-08-10T15:14:00Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220795#M64870 <P>Actually I forgot to notice this before:<BR /> <CODE>eval delta=tostring(_time-prevtime, "duration")</CODE> is not outputting any value.. Looking more into it.</P> <P>Thanks<BR /> Vikram Yerneni</P> Wed, 10 Aug 2016 15:17:57 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220795#M64870 vikramyerneni 2016-08-10T15:17:57Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220796#M64871 <P>Try this runanywhere sample</P> <PRE><CODE>index=_internal | streamstats window=1 current=f earliest(_time) as nextTime by host sourcetype | eval duration=nextTime-_time | table _time nextTime duration </CODE></PRE> Wed, 10 Aug 2016 17:31:58 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220796#M64871 sundareshr 2016-08-10T17:31:58Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220797#M64872 <P>Hey Sundar, I was off for last two days. Let me validate it and will get back to you.<BR /> Thanks man..</P> Fri, 12 Aug 2016 14:40:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220797#M64872 vikramyerneni 2016-08-12T14:40:08Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220798#M64873 <P>Hi Sundar,</P> <P>Yes the "runanywhere sample" worked with output (Duration as "0"). However the earlier query you provided actually is not providing output to <CODE>eval delta=tostring(_time-prevtime, "duration") |</CODE>. I do see the logic here taking the previous provided time and subtracting it from _time (current time) which will give the time in between these two events. <BR /> Not sure why I did multiple combinations but still its not outputting it.</P> <P>Any thoughts here..?</P> <P>Thanks<BR /> Vikram Y</P> Fri, 12 Aug 2016 15:31:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220798#M64873 vikramyerneni 2016-08-12T15:31:22Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220799#M64874 <P>Try the updated search..</P> Fri, 12 Aug 2016 17:05:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220799#M64874 sundareshr 2016-08-12T17:05:54Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220800#M64875 <P>Same response Sundar. Still its showing "Delta" field with blank values. I cant upload a screenshot to this ticket yet as I dont have enough Karma points..</P> <P>Thanks<BR /> Vikram Y</P> Fri, 12 Aug 2016 18:07:39 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220800#M64875 vikramyerneni 2016-08-12T18:07:39Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220801#M64876 <P>What do you get when you run this</P> <PRE><CODE>index=dynatrace "failed events" | reverse | streamstats window=1 current=f earliest(_time) as prevTime by host transactionname | table host transactionname _time prevTIme </CODE></PRE> Fri, 12 Aug 2016 18:16:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220801#M64876 sundareshr 2016-08-12T18:16:56Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220802#M64877 <P>Lets try a different approach</P> <PRE><CODE>index=dynatrace "failed events" transactioname=* | stats earliest(_time) as start latest(_time) as end by host transactionname | eval duration=tostring(round(end-start, 0), "duration") </CODE></PRE> Fri, 12 Aug 2016 18:38:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220802#M64877 sundareshr 2016-08-12T18:38:35Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220803#M64878 <P>Now it worked.. "Reverse" made differnece.. Plus one thing I noticed is the query is Case-sensitive.. Atleast now I am getting right data into the Statistics page.. <BR /> Let me validate the data and will get back to you soon Sundar..<BR /> Thanks man..</P> Fri, 12 Aug 2016 18:53:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220803#M64878 vikramyerneni 2016-08-12T18:53:29Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220804#M64879 <P>Awesome. Once validated, please accept/upvote the answer</P> Fri, 12 Aug 2016 18:54:31 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220804#M64879 sundareshr 2016-08-12T18:54:31Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220805#M64880 <P>Absolutely sir.. will do..<BR /> Thanks again dude..</P> Fri, 12 Aug 2016 19:00:59 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220805#M64880 vikramyerneni 2016-08-12T19:00:59Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220806#M64881 <P>Alright the data looks good.<BR /> One last point I noticed here is the logic provided here will take the first _time and subtracts from the next one and because of that the first column in the result the "delta" output is blank which dosen't make the visualization graphs a blank. <BR /> Is there a way to remove this first line from the output or fill it with "0" for any blank values? <BR /> Let me know sir..</P> <P>Thanks<BR /> Vikram Yerneni</P> Fri, 12 Aug 2016 19:21:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220806#M64881 vikramyerneni 2016-08-12T19:21:08Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220807#M64882 <P>@vikramyerneni, just add <CODE>fillnull value=0 delta</CODE> that will fill null values with 0, in your case, the first row</P> Fri, 12 Aug 2016 19:23:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-calculate-the-time-difference-between/m-p/220807#M64882 sundareshr 2016-08-12T19:23:16Z