alert search with subsearch

[damucka]

Hello,

I have an alert which selects from the database and whenever entries come back, the alert is triggered.
Now, I would like to implement the subsearch there and depending if it brings any result back, the main part of the alert should be triggered.

The main search for the alert:

| dbxquery query="select * from zkpiv_lstm_score" connection="HANA_MLBSO" | table RCA_TO_REPORT SYSID HOST TIMESTAMP CPU_CONSUMERS MEMORY_CONSUMERS CPU SYSTEM_CPU MEMORY_USED MEMORY_ALLOCATION_LIMIT PING_TIME CONNECTION_COUNT BLOCKED_TRANSACTION_COUNT STATEMENT_COUNT COMMIT_ID_RANGE  CS_READ_COUNT CS_WRITE_COUNT CS_MERGE_COUNT CS_UNLOAD_COUNT ACTIVE_THREAD_COUNT WAITING_THREAD_COUNT     

When the result comes back it means our Anomaly Detection algorithms found an issue and the alert should be triggered, so far so good. But in the same time we have also an alert searching for the system Crash Dumps. Obviously when we find a Crash Dump, we do not need to alert on the anomalies anymore. So, what I would like to achieve is, that if the subsearch for the Crash Dump is true, then the main search for the Anomaly Detection should NOT be true and thus alert not triggered.
The subsearch for the Crash Dump:

| search [index=mlbso_changelog  (crash_context OR crash_stack OR crash_shortinfo)   sourcetype = BWP_crashdumps  NOT "Table of contents" earliest=-60m latest=now | reverse]   

How would I do this?

Kind Regards,
Kamil

[jvishwak]

How will you relate crash dump and anomaly detection? if it can related based on SYSID / HOST, or any other field in result from your Anomaly detection query, you can simple do join with crash dump query. You can filter the event for which any match is found.
I think u will also need to check if the timing of Crash dump and anomaly detection, Timing of the crash dump should be earlier than Anomaly detection. So, for every match with Crash dump get the time of crash dump log as well and compare it with time of Anomaly detection time.

[damucka]

Hello @jvishwak

Thank you.
However about the logic of when to trigger what I am pretty clear. The point was rather how would I technically join the both search and subsearch in the way that when the subsearch delivers any result, then the whole alert condition consisting of search+subsearch is false / not triggering the alert.
How would it have to look like?

Regards,
Kamil

[jvishwak]

When your whole query (search and sub-search) doesn't return any result then the Alert will anyways will not trigger.
You do type=left join, so it will get the result always, even if there is no match in Crash log. You get a value out of the join which is specific to Crash log. If match is found, you will have a value for this field post join, else this field will be blank. Then you can have where condition to check blank value for this field.
For Example:
| dbxquery query="select * from zkpiv_lstm_score" connection="HANA_MLBSO" | table RCA_TO_REPORT SYSID HOST TIMESTAMP CPU_CONSUMERS MEMORY_CONSUMERS CPU SYSTEM_CPU MEMORY_USED MEMORY_ALLOCATION_LIMIT PING_TIME CONNECTION_COUNT BLOCKED_TRANSACTION_COUNT STATEMENT_COUNT COMMIT_ID_RANGE CS_READ_COUNT CS_WRITE_COUNT CS_MERGE_COUNT CS_UNLOAD_COUNT ACTIVE_THREAD_COUNT WAITING_THREAD_COUNT
| join type=left SYSID [ search index=mlbso_changelog (crash_context OR crash_stack OR crash_shortinfo) sourcetype = BWP_crashdumps NOT "Table of contents" earliest=-60m latest=now | fields SYSID, X, Y , Z| eval logX = "CrashLog"]
| table SYSID, logX, ......
| where isnull(logX)