Compare two table searchers and combine to single table (where condition)

[irvanrak]

Hi,

Got two different searches result in to corresponding table format, want to achieve something like "select b.column1, b.column2 from table a, table b where a.column1 = b.column1" in single search.

Table 'a' contain a single column named TransactionID where table 'b' contain two columns named TransactionID , Errors

Search 1 : index=app source=.log "FATAL" | rex "some value (?.+) somemore" | table TID
Search 2 : index=app source=.log "ERROR_TEXT" | rex "some value (?.+) somemore" | rex field=_raw "some txt (?.+)" | table TID, ErrorText

Required output like "select b.column1, b.column2 from table a, table b where a.column1 = b.column1" in single search.

[somesoni2 · Accepted solution]

Try like this

 index=app source=*.log ("FATAL" OR "ERROR_TEXT" )
| fields _raw | eval type=if(match(_raw,"FATAL"),"FATAL","ERROR")
| rex "some value (?<TID>.+) somemore" 
| rex field=_raw "some txt (?<ErrorText>.+)" 
| stats values(ErrorText) as ErrorText dc(type) as type by TID
| where type=2

The where clause ensures that we're only considering TIDs which are available in both type of records (fatal and error).

[irvanrak]

Thanks a lot, got exactly what i was looking for.