How to compare same field value with two different Searches?

[Lavender]

Hi,

I have same field that value has to compared between 2 search queries. So, Kindly help on below.

index=xyz |search component=gateway appid=12345 message="*|osv|*"
|rex "trace-id.(?<RequestID>\d+)"

|fillnull value=NULL RequestID

|search RequestID!=NULL

|table _time,Country,Environment,appID,LogMessage

|append [search index=xyz |search appid=12345 message="*|osv|*"  level="error"
|search `mymacrocompo` 

|rex "trace-id.(?<RequestID1>\d+)"

|fillnull value=NULL RequestID1

|search RequestID1!=NULL
|table LogMessage1]

|eval Errorlogs=if(RequestID=RequestID1,"LogMessage1", "NULL")

In the above query, we have RequestID in the main query and the sub query as well. we have to find out the error logs based on RequestID which means if RequestID matches with RequestID1, need to dispaly the LogMessage1.

[gcusello]

Hi @Lavender,

you have to use the same field name to correlate the two searches, something like this:

index=xyz component=gateway appid=12345 message="*|osv|*"
| rex "trace-id.(?<RequestID>\d+)"
| search RequestID=*
| table _time,Country,Environment,appID,LogMessage
| append [search index=xyz appid=12345 message="*|osv|*" level="error" `mymacrocompo` 
| rex "trace-id.(?<RequestID>\d+)"
| search RequestID=*
| table RequestID LogMessage1 ]
| stats 
   earliest(_time) AS _time
   values(Country) AS Country
   values(Environment) AS Environment
   values(appID) AS appID
   values(LogMessage) AS LogMessage
   values(eval(if(level="error",LogMessage1, "NULL"))) AS Errorlogs
   BY RequestID

Ciao.

Giuseppe

[Lavender]

In results, we are getting Error Log message if available but our requirement is to get the log message only if Request ID is matching with RequestID of Sub Query.
please help

[gcusello]

Hi @Lavender ,

in this case, you have to add an additional condition:

index=xyz component=gateway appid=12345 message="*|osv|*"
| rex "trace-id.(?<RequestID>\d+)"
| search RequestID=*
| eval env=main_search
| table _time Country Environment appID LogMessage env
| append [search index=xyz appid=12345 message="*|osv|*" level="error" `mymacrocompo` 
| rex "trace-id.(?<RequestID>\d+)"
| search RequestID=*
| eval env=sub_search
| table RequestID LogMessage1 env ]
| stats 
   earliest(_time) AS _time
   values(Country) AS Country
   values(Environment) AS Environment
   values(appID) AS appID
   values(LogMessage) AS LogMessage
   values(eval(if(level="error",LogMessage1, "NULL"))) AS Errorlogs
   dc(env) AS env_count
   BY RequestID
| where env_count=2

Ciao.

Giuseppe

[Lavender]

Hi @gcusello ,

Thanks for your answers. 

[richgalloway]

Use the same field name in the main search and in the appended search then use the stats command to join the results on common RequestID values.

index=xyz |search component=gateway appid=12345 message="*|osv|*"
|rex "trace-id.(?<RequestID>\d+)"
|fillnull value=NULL RequestID
|search RequestID!=NULL
|table _time,Country,Environment,appID,LogMessage
|append [search index=xyz |search appid=12345 message="*|osv|*"  level="error"
  |search `mymacrocompo` 
  |rex "trace-id.(?<RequestID>\d+)"
  |fillnull value=NULL RequestID
  |search RequestID!=NULL
  |table LogMessage1]
| stats values(*) as * by RequestID

The reason why |eval Errorlogs=if(RequestID=RequestID1,"LogMessage1", "NULL") does work is because the append command puts the events with RequestID1 on different "rows" than events with RequestID.  Since SPL looks at one event (row) at a time, no event will have both RequestID and RequestID1.