Other Usage

How to compare same field value with two different Searches?

Lavender
Loves-to-Learn Everything

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.

 

Labels (1)
Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Lavender
Loves-to-Learn Everything

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

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Lavender
Loves-to-Learn Everything

Hi @gcusello ,

 

Thanks for your answers. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...