How to join and get stats from same index?

[vikas_gopal]

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

[anmolpatel · Accepted solution]

@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]

Worked like Charm the mistake I was doing was is values(date) . That did a trick . Thanks