How to edit my search to separate values in a column into two columns in my resulting table?

[prashanthberam]

I have the table like this:

time           info    id     response time
start time1    in      571          
end time1      out     571    10.01
start time2    in      560               
end time2      out     560    11.01

but I want to display it like this:

starttime1     end time1     id     responsetime
starttime2     end time2     id     responsetime

My search is like this:

index=**** source="*****_****"   "getProcedureDetailBlueChip" OR "getProcedureDetailBlueChipResponse" AND "Outbound Message" OR "Inbound Message" |rex "(?.{23})"|rex field=_raw "INFO  :(?.*)"|rex field=_raw "ID:(?.*)"|sort _time|streamstats current=f last(_time) as LastTime by ID,source|eval ResponseTime=_time-LastTime|sort -ID|table Time,INFO,ID,ResponseTime

I have attached the table pic too.

Can anybody help please? Thanks in advance.

[sundareshr]

Try this

index=**** source="*****_****"   "getProcedureDetailBlueChip" OR "getProcedureDetailBlueChipResponse" AND "Outbound Message" OR "Inbound Message" | rex "(?<info>Inbound|Outbound)" | rex "ID:(?<id>.*)" | chart earliest(_time) as time over id by info | eval responsetime=outbound-inbound | eval responsetime=tostring(responsetime, "duration") | convert ctime(*bound) AS *bound

[prashanthberam]

Thanks sundar..it looks somewhat working but I have duplicates in the id's but when am searching the different sources I can achieve that cloud you please tell me where I can include source in my code.

[sundareshr]

What do you mean "duplicates in the id"? Try adding this before the chart segment. | eval id=source."::".id

[snoobzilla]

| eval starttime=if(INFO="Inbound Message", Time, null())
| eval endtime=if(INFO="Outbound Message", Time, null()
| stats values(starttime) AS starttime values(endtime) AS endtime values(ResponseTime) by ID

Above should get you close.

[prashanthberam]

yes ...they have duplicates id's

[prashanthberam]

am not getting any results in the Time field..

[snoobzilla]

You had a Time field in the table, was assuming you would append what I posted onto your query.

Are the IDs unique or do they repeat? Whats max response time? How many events would you search over... if only a few thousand transaction could be a good fit.