How to exclude result from query?

[karthi2809]

Hi Guys,

In my scenario i need show error details for correlation id .There are field called tracePoint="EXCEPTION" and message field with PRD(ERROR):

In some cases we have exception first after that the transaction got success.So at that time i want to ignore the transaction in my query.But its not ignoring the success correlationId in my result

index="mulesoft" applicationName="s-concur-api" environment=PRD (tracePoint="EXCEPTION" AND message!="*(SUCCESS)*")|transaction correlationId | rename timestamp as Timestamp correlationId as CorrelationId tracePoint as TracePoint content.ErrorType as Error content.errorType
as errorType  content.errorMsg as ErrorMsg content.ErrorMsg as errorMsg
| eval ErrorType=if(isnull(Error),"Unknown",Error)
| dedup CorrelationId |eval errorType=coalesce(Error,errorType)|eval Errormsg=coalesce(ErrorMsg,errorMsg)
|table CorrelationId,Timestamp, applicationName, locationInfo.fileName, locationInfo.lineInFile, errorType, message,Errormsg
 | sort -Timestamp

[scelikok · Accepted solution]

You are right, I missed to filter again for exceptions. Please try below, you should see only correlationId exceptions that have no SUCCESS.

index="mulesoft" applicationName="s-concur-api" environment=PRD 
| eventstats values(tracePoint) as TracePoints values(message) as Messages by correlationId 
| search TracePoints="EXCEPTION" Messages!="*(SUCCESS)*" 
| fields - TracePoints - Messages
| search tracePoint="EXCEPTION"  
| transaction correlationId 
| rename timestamp as Timestamp correlationId as CorrelationId tracePoint as TracePoint content.ErrorType as Error content.errorType
    as errorType content.errorMsg as ErrorMsg content.ErrorMsg as errorMsg 
| eval ErrorType=if(isnull(Error),"Unknown",Error) 
| dedup CorrelationId 
| eval errorType=coalesce(Error,errorType) 
| eval Errormsg=coalesce(ErrorMsg,errorMsg) 
| table CorrelationId,Timestamp, applicationName, locationInfo.fileName, locationInfo.lineInFile, errorType, message,Errormsg 
| sort -Timestamp

[scelikok]

Hi @karthi2809,

Can you please try below with eventstats?

index="mulesoft" applicationName="s-concur-api" environment=PRD 
| eventstats values(tracePoint) as TracePoints values(message) as Messages by correlationId 
| search TracePoints="EXCEPTION" Messages!="*(SUCCESS)*" 
| fields - TracePoints - Messages
| transaction correlationId 
| rename timestamp as Timestamp correlationId as CorrelationId tracePoint as TracePoint content.ErrorType as Error content.errorType
    as errorType content.errorMsg as ErrorMsg content.ErrorMsg as errorMsg 
| eval ErrorType=if(isnull(Error),"Unknown",Error) 
| dedup CorrelationId 
| eval errorType=coalesce(Error,errorType) 
| eval Errormsg=coalesce(ErrorMsg,errorMsg) 
| table CorrelationId,Timestamp, applicationName, locationInfo.fileName, locationInfo.lineInFile, errorType, message,Errormsg 
| sort -Timestamp

[karthi2809]

@scelikok  Yes its working But its showing all the timestamp and all the messages. Just i want to show error message not all transaction messages for the correlationId

[bowesmana]

It looks like you are excluding all the message=SUCCESS events, so you will never see them in the transaction data. If you want to exclude them, you will need to remove that message!="*(SUCCESS)*" constraint.

Then your transaction will have the SUCCESS event included, so at that point, you can then filter out those events that have both succeeded then failed.

However, you will need to take care of ordering - you know your data, but can the SUCCESS come AFTER the fail?