Splunk Search

Search Advanced Nested query many line

mclane1
Path Finder

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.

Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

View solution in original post

mclane1
Path Finder

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.

0 Karma

sundareshr
Legend

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 
0 Karma

inventsekar
Super Champion

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
0 Karma

mclane1
Path Finder

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

Regards,

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.