How to compute subtraction?

[m7787579]

Start Time End time Reason Difference
05/09/2016 18:05 05/12/2016 14:55 Target Up

05/12/2016 14:55 05/12/2016 15:22 Target Down 21:17
12/05/2016 15:02 13/05/2016 10:37 Target Up

12/05/2016 14:55 13/05/2016 14:23 Target Down 23:21

I have to calculate for how much time target was down ,
Difference=(End Time-Start Time)
Difference=(05/12/2016 15:22-05/09/2016 18:05 )=21:17
Similarly
Difference=(13/05/2016 14:23-12/05/2016 15:02)=23:21

This Difference i have calculated with the help of Excel.
How can i do this subtraction from Splunk ?
And i only have these three column as input (Start Time,End Time,Reason)

Thanks in Advance
Tarun Malhotra

[niketn]

[Updated Answer based on details provided]

You can make use of streamstats command to pick End Time from previous row. Your series should be sorted ascending or descending for streamstats. In this case I have sorted in reverse chronological order since the duration for latest event can not be calculated.

Following query mocks the data as per your question. Query till sort - _time mocks data followed by actual query for your answer.

| makeresults
| eval "Start Time"="05/09/2016 18:05"
| eval "End Time"="05/12/2016 14:55"
| eval "Start Time"=strptime('Start Time',"%d/%m/%Y %H:%M")
| eval "End Time"=strptime('End Time',"%d/%m/%Y %H:%M")
| eval _time='Start Time'
| append [
    | makeresults
    | eval "Start Time"="05/12/2016 14:55"
    | eval "End Time"="05/12/2016 15:22"
    | eval "Start Time"=strptime('Start Time',"%d/%m/%Y %H:%M")
    | eval "End Time"=strptime('End Time',"%d/%m/%Y %H:%M")    
    | eval _time='Start Time'
  ]
| append [
    | makeresults
    | eval "Start Time"="12/05/2016 15:02"
    | eval "End Time"="13/05/2016 10:37"
    | eval "Start Time"=strptime('Start Time',"%d/%m/%Y %H:%M")
    | eval "End Time"=strptime('End Time',"%d/%m/%Y %H:%M")    
    | eval _time='Start Time'
  ]
| append [
    | makeresults
    | eval "Start Time"="12/05/2016 14:55"
    | eval "End Time"="13/05/2016 14:23"
    | eval "Start Time"=strptime('Start Time',"%d/%m/%Y %H:%M")
    | eval "End Time"=strptime('End Time',"%d/%m/%Y %H:%M")    
    | eval _time='Start Time'
  ]
| fieldformat _time=strftime(_time,"%d/%m/%Y %H:%M")
| fieldformat "Start Time"=strftime('Start Time',"%d/%m/%Y %H:%M")
| fieldformat "End Time"=strftime('End Time',"%d/%m/%Y %H:%M")
| sort -_time
| streamstats current=f global=f window=1 last("End Time") as "Last End Time"
| eval duration=('Last End Time'-'Start Time')/(60*60)
| fieldformat "Last End Time"=strftime('Last End Time',"%d/%m/%Y %H:%M")
| table _time "End Time" "Start Time" "Last End Time" duration

PS: My points from previous answer are still applicable around usage of epoch time, string time, fieldformat and case sensitivity of field name also space in the field name.

---

[Previous Answer]

If you have Start Time and End Time as epoch time you can directly perform the following which should give you duration as Seconds.

| eval duration='End Time'-'StartTime'

If the Start Time and End Time are string Time and not epoch, then you would need to convert them to epoch before computing the difference using strptime. Read the same on Splunk documentation. Following is a run anywhere example (first 4 lines mocks one of sample data in the question😞

| makeresults
| fields - _time
| eval "Start Time"="05/09/2016 18:05"
| eval "End Time"="05/12/2016 14:55"
| eval "Start Time"=strptime('Start Time',"%d/%m/%Y %H:%M")
| eval "End Time"=strptime('End Time',"%d/%m/%Y %H:%M")
| eval duration=('End Time'-'Start Time')/(60*60)
| fieldformat "Start Time"=strftime('Start Time',"%d/%m/%Y %H:%M")
| fieldformat "End Time"=strftime('End Time',"%d/%m/%Y %H:%M")
| rename duration as "Duration ( in hours)"

PS:
Based on couple of examples in your question your dates seem to be in dd/mm/yyyy HH:MM format. Based on which , the difference between 05/12/2016 15:22-05/09/2016 18:05 should be 3+ months. I am not sure how you have calculated 21:17?

Field Names are Case Sensitive and Space in Field Names requires you to escape the same in various SPL commands. Make sure Field Names match exactly as per your event data (in you example "End Time" is in both lower and upper case.)

[DalJeanis]

@niketnilay - I believe you meant "almost 3 days" rather than 3+ months...

[niketn]

@DalJeanis, I also started off thinking second position is for date, but seems like it is for month. Look at third example, month can not be 13... 🙂

So, strangely in the example which I have picked it is indeed 3+ months 😉

[m7787579]

In my question End time belong to 2 row and start time belong to 1 row similarly for next end time belong to 4 row and start time belong to 3 row.

I want to subtract End_Time(2row)-Start_Time(1row).

I hope i am making sense.
Apologies for not posting the clear requirement.

[niketn]

@m778759, I have updated my answer with streamstats to match your requirement. Similar results may also be achieved from delta command.