How to edit search to find the amount time an event is in multi states?

[sarfarajsayyad]

I have an index with 30+ fields. One of the field is state. I want to find amount of time an event is in a particular states (comma separated states).

I have a macro like below

[TimeInState(1)]
args = States
join type=left data_id [search index="data_log" log_msg="State Established: $States$" | dedup log_id | sort 0 _time by id | delta _time as "offclock" p=1 | ......remaining search query
iseval = 0

This macro is working fine with single input (States="Open"). What changes i need to do to work with multiple comma separated states(States="Open,Working")?

I have tried using | makemv delim="," states | top states | but no success
and another issue is i want to do this before first pipe ( | ). i.e search index="datat_log" log_msg="State Established: $TicketStates$" Some logic here

Any advice and suggestions will be greatly appreciated
/Sarfaraj

[maciep]

Maybe you can use a subsearch to transform the States parameter the way you want? For example:

join type=left data_id [search index="data_log" [| stats count | eval log_msg =split($States$,",") | mvexpand log_msg | eval log_msg="State Established: " . log_msg | fields - count] | dedup log_id | . . . 

You'll probably have to change the logic around the rest of the query to account for multiple states though I think.