how to show start ,end time , duration in a table

[vikas_gopal]

Hi Experts ,

I know this can be achieved in splunk , I have data like below

name,status,date
erp,200,2019-10-01 08:28:33
erp,200,2019-10-01 08:29:33
erp,503,2019-10-01 08:30:33
erp,,2019-10-01 08:31:33
erp,,2019-10-01 08:32:33
erp,503,2019-10-01 08:33:33
erp,200,2019-10-01 08:34:33
erp,200,2019-10-01 08:35:33
erp,200,2019-10-01 08:36:33

I want to show something like below in a table

name  from_date                       to_date            status           duration
erp     2019-10-01 08:28:33      2019-10-01 08:29:33     200              1 min
erp     2019-10-01 08:30:33     2019-10-01 08:31:33      503              1 min
erp    2019-10-01 08:31:33     2019-10-01 08:32:33       ""               1 min
erp    2019-10-01 08:33:33    2019-10-01 08:36:33        200              4 min

So the idea is to get the duration as per status , if my service has like 503 status for how long that status was .

Any help will be highly appreciated

Regards
VG

[woodcock]

Your math and mockedup results are inconsistent but this should get you where you can adjust to suit:

| makeresults 
| eval _raw="name,status,date
 erp,200,2019-10-01 08:28:33
 erp,200,2019-10-01 08:29:33
 erp,503,2019-10-01 08:30:33
 erp,,2019-10-01 08:31:33
 erp,,2019-10-01 08:32:33
 erp,503,2019-10-01 08:33:33
 erp,200,2019-10-01 08:34:33
 erp,200,2019-10-01 08:35:33
 erp,200,2019-10-01 08:36:33" 
| multikv forceheader=1 
| eval _time = strptime(date, "%Y-%m-%d %H:%M:%S") 
| sort 0 - _time 
| table _time name status 
| streamstats count AS _serial 
| eval status = coalesce(status, "NULL") 
| streamstats current=f last(status) AS next_status last(_time) AS next_time 
| eval next_status = coalesce(next_status, status) 
| streamstats count(eval(status!=next_status)) AS sessionID 
| eventstats min(_time) AS from_date max(next_time) AS to_date count BY status sessionID
| eval duration = to_date - from_date 
| sort 0 _time 
| fieldformat from_date = strftime(from_date, "%Y-%m-%d %H:%M:%S") 
| fieldformat to_date = strftime(to_date, "%Y-%m-%d %H:%M:%S")

[vikas_gopal]

Thank you woodcock, first of all my apology on the inconsistent data .Because I need only from_date,to_date,name duration status in my table so just added below to end of the query .

|table from_date,to_date,name duration status |dedup from_date,to_date,name duration status

but with this approach if new name comes in with same status then it is showing wrong duration. I have just added new line at the bottom so now I have 2 names erp and trp but for trp duration is 300
trp,200,2019-10-01 08:37:33