FInd different value from "-" on column

[_Mauro_Costa_]

Hello
I have a query that gives me the data below:

_time                                 | id                 | order_id   | job             | user_id
------------------------------------------------------------------------------------
2021-06-08 17:00:00 | 2240905 | -                   | done         | 23
------------------------------------------------------------------------------------
2021-06-08 17:00:00 | 2240844 | -                   | done         | 23
------------------------------------------------------------------------------------
2021-06-08 12:00:00 | 2240905 | -                   | start          | 167
------------------------------------------------------------------------------------
2021-06-15 10:00:00 | 2240844 | -                   | start          | 102
------------------------------------------------------------------------------------
2021-06-15 10:00:00 | 2240905 | 1066899 | allocated | 23
------------------------------------------------------------------------------------
2021-06-15 09:00:00 | 2240844 | 1055788 | allocated | 23

for each id, i need to find job "start" to have user_id and _time, but i also need order_id, how can i do this?
I need something like this:

_time                                 | id                 | order_id   | job             | user_id
------------------------------------------------------------------------------------
2021-06-08 12:00:00 | 2240905 | 1066899 | start          | 167
------------------------------------------------------------------------------------
2021-06-15 10:00:00 | 2240844 | 1055788 | start          | 102
------------------------------------------------------------------------------------
Thanks

[venkatasri · Accepted solution]

Hi @_Mauro_Costa_ 

Can you try this,

<your_search>
| eventstats max(order_id) as order_id by id 
| search job=start 
| stats max(order_id) as order_id, max(user_id) as user_id, values(job) as job, earliest(_time) as time by id 
| convert ctime(time) as time

--

An upvote would be appreciated if this reply helps!

[_Mauro_Costa_]

@venkatasri thanks
this worked for me " | eventstats max(order_id) as order_id by id "