How to combine three different queries(condition) to create one email alert?

[anna]

1st Query :

StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" HasError__c=false Transaction_Log__c="*"
| eval message = "200andNo matching records were found"
| where like(_raw,"%".message."%")
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "400andDealer Code provided is invalid"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "400andDealer Type provided is invalid"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "400andNo Dealer Code was provided"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "400andNo Dealer Type was provided"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "400andInvalid input data"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval message = "500andCannot deserialize request body"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:exception_log__c" ErrorCode__c=500 Interface_Name__c=StoreManagementAPI
| eval message = "Unexpected character"
| where like(_raw,"%".message."%")]
| append
[search StoreManagementAPI index=b2cforce sourcetype="sfdc:exception_log__c" ErrorCode__c=500 Interface_Name__c=StoreManagementAPI
| where Error_Description__c != "Unexpected character ('}' (code 125)): was expecting double-quote to start field name at [line:4, column:6]"
| table _time,Error_Description__c
| rename Error_Description__c as message]
| timechart span=30m count by message
| eval eval threshold = 25

2nd query :

StoreManagementAPI index=b2cforce sourcetype="*" "attributes.type"="*"
| stats count(sourcetype) as total_events
| where total_events > 480

3rd query :

StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c" Transaction_Log__c="*"
| eval _raw= Transaction_Log__c
| rex max_match=0 "timestamp[[:punct:]]+(?<timestamp>[^\\\"]+)"
| eval first_timestamp=mvindex(timestamp,0), last_timestamp=mvindex(timestamp, -1)
| eval first_ts = strptime(first_timestamp, "%Y-%m-%dT%H:%M:%S.%3N%Z"), last_ts = strptime(last_timestamp, "%Y-%m-%dT%H:%M:%S.%3N%Z")
| eval diff = last_ts - first_ts
| stats avg(diff) as average

[gcusello · Accepted solution]

Hi @anna,

at first I hint to semplify your first search because, if your subsearches has more than 50,000 results, it could give wrong results, and then it's a very heavy search.

You could instead use a different approach:

StoreManagementAPI index=b2cforce sourcetype="sfdc:transaction_log__c"  Transaction_Log__c="*"
| eval message=case(
   like(message,"%200andNo matching records were found%"),"200andNo matching records were found",
   like(message,"%400andDealer Code provided is invalid%"),"400andDealer Code provided is invalid",
   like(message,"%400andDealer Type provided is invalid%"),"400andDealer Type provided is invalid",
   like(message,"%400andNo Dealer Code was provided%"),"400andNo Dealer Code was provided",
   like(message,"%400andNo Dealer Type was provided%"),"400andNo Dealer Type was provided",
   like(message,"%400andInvalid input data%"),"400andInvalid input data",
   like(message,"%500andCannot deserialize request body%"),"500andCannot deserialize request body",
   like(message,"%Unexpected character%"),"Unexpected character",
   Error_Description__c!="Unexpected character ('}' (code 125)): was expecting double-quote to start field name at [line:4, column:6]")
| timechart span=30m count by message
| eval eval threshold = 25

Then, maybe I didn't understand, but I understood that you want to merge results from a timechart command (with timestamps) with two stats command (without timestamp), and each of them with a filter on the quantity, could you better describe what you want to have as result?

Ciao.

Giuseppe

[anna]

Hi @gcusello ,

actually i want to create one email alert for this three queries that's why i need to combine this queries.

[gcusello]

Hi @anna,

I understand you need, but you're trying to display three kind of results, the problem isn't to execute searches but how to display results.

could you share a sample of the result you're waiting?

Ciao.

Giuseppe

[ITWhisperer]

You could try creating a dashboard with all three queries in and schedule a PDF delivery?