Search Advanced Nested query many line

[mclane1]

Hello,

I have lot of line with expression like this :

code=1 executionTime=n ident=XXX

and lot of line with expression like this :

code=2 executionTime=m otherIdent=XXX ident=YYYY

I would like substract the executionTime of line with code 1 with line in code 2.
I try with this query but it doesn't work :

index=my_index code=1 [search index=my_index code=2 | RENAME executionTime as retrieveExecutionTime| RENAME code as retrieveCode| RENAME ident AS retrieveIdent |RENAME otherIdent as ident| FIELDS ident] | TABLE ident,code, executionTime, retrieveExecutionTime, retrieveCode

Columns retrieveExecutionTime, retrieveCode are empty.
How can I have one line by ident/otherIdent with all necessary information ?

Thanks in advance.

[somesoni2 · Accepted solution]

Try like this

index=myindex (code=1 OR code=2) 
| fields ident otherIdent code executionTime
| eval ident=if(code=1,ident,otherIdent)
| eval retrieveIdent=if(code=2,ident,null())
| eval retrieveCode=if(code=2,code,null())
| eval retrieveExecutionTime=if(code=2,executionTime,null())
| stats values(*) as * by ident

[mclane1]

Hello;
I understand idea.
I have table like this :

||code||ident||otherIdent||executionTime||
|1|i1|oi1|t1|
|2|X|i1|t2|

and I can transform this table like this (with eval)

||code||ident||otherIdent||executionTime||id||executionTime1||executionTime2||...||
|1|i1|oi1|t1|i1|t1|null|...|
|2|X|i1|t2|i1|null|t2|...|

with stats values do only one line.

Very good idea.

Thank you.

[sundareshr]

Try this

index=my_index  code=1 OR code=2 | streamstats count by code | streamstats window=1 current=f values(executionTime) as retrieveExecutionTime by count | table _time code count executionTime retrieveExecutionTime 

[inventsekar]

Please try append command -

 index=my_index code=1 
|append [search index=my_index code=2 | RENAME executionTime as retrieveExecutionTime| RENAME code as retrieveCode| RENAME ident AS retrieveIdent |RENAME otherIdent as ident| FIELDS ident] 
| TABLE ident,code, executionTime, retrieveExecutionTime, retrieveCode

[mclane1]

Thanks for answer.
Unfortunatly, it's always empty for retrieve... columns.

Regards,