Hi Experts,
I have data set like below from same index but from different sourcetype, common field on which I can join is aapid, app_id. I want to only show those app id which take more than 20 min time for approval .
Sourcetye=created
date,status,appid
18/Oct/2018 05:05:02,created,1234
18/Oct/2018 05:06:02,created,12345
18/Oct/2018 05:07:02,created,123456
Sourcetye=approved
date,status,app_id
18/Oct/2018 05:25:02,approved,1234
18/Oct/2018 05:40:02,approved,12345
On the above sample data set I am expacting a table like below.
Appid,Created time , Approved time ,totoal_time
12345,18/Oct/2018 05:06:02,18/Oct/2018 05:40:02,34min
Regards
VG
@vikas_gopal something as such ?
| makeresults
| eval _raw = "
sourcetype, date,status,appid
created, 18/Oct/2018 05:05:02,created,1234,
created, 18/Oct/2018 05:06:02,created,12345,
created, 18/Oct/2018 05:07:02,created,123456"
| multikv forceheader=1
| stats values(date) as created_date by appid
| join type=outer appid [ |makeresults
| eval _raw = "sourcetype,date,status,app_id
approved, 18/Oct/2018 05:25:02,approved,1234
approved, 18/Oct/2018 05:40:02,approved,12345
approved, 18/Oct/2018 05:17:02,created,123456"
| multikv forceheader=1
| stats values(date) as approved_date by app_id
| rename app_id as appid]
| where approved_date!= ""
| eval total_time = strftime((strptime(approved_date, "%d/%b/%Y %H:%M:%S") - strptime(created_date, "%d/%b/%Y %H:%M:%S")), "%M")
| where total_time >= 20
| eval "Total Time" = total_time." mins"
| rename appid as Appid approved_date as "Approved Date" created_date as "Created Date"
| fields Appid "Created Date" "Approved Date" "Total Time"
@vikas_gopal something as such ?
| makeresults
| eval _raw = "
sourcetype, date,status,appid
created, 18/Oct/2018 05:05:02,created,1234,
created, 18/Oct/2018 05:06:02,created,12345,
created, 18/Oct/2018 05:07:02,created,123456"
| multikv forceheader=1
| stats values(date) as created_date by appid
| join type=outer appid [ |makeresults
| eval _raw = "sourcetype,date,status,app_id
approved, 18/Oct/2018 05:25:02,approved,1234
approved, 18/Oct/2018 05:40:02,approved,12345
approved, 18/Oct/2018 05:17:02,created,123456"
| multikv forceheader=1
| stats values(date) as approved_date by app_id
| rename app_id as appid]
| where approved_date!= ""
| eval total_time = strftime((strptime(approved_date, "%d/%b/%Y %H:%M:%S") - strptime(created_date, "%d/%b/%Y %H:%M:%S")), "%M")
| where total_time >= 20
| eval "Total Time" = total_time." mins"
| rename appid as Appid approved_date as "Approved Date" created_date as "Created Date"
| fields Appid "Created Date" "Approved Date" "Total Time"
Worked like Charm the mistake I was doing was is values(date) . That did a trick . Thanks