Help with a custom co-relation search!!!!

[swaguzari]

I'm having a problem creating an alert for following scenario:

Data source: index=mail sourcetype=pps_messagelog (interesting fields = guid, final_action). Basically I want a search which would fire up an alert whenever there are events which have same "guid" but more than one "final_action". Or, in another way, if there is an event with "final_action=continue", which matches an event with "final_action=discard", with SAME "guid" being the matching criteria.

Sample Data:

{"guid": "Irhblj4vS9DsfIwHAFbT8pbzf2mZQISa", "msg": {"parsedAddresses": {"to": ["bruce.banner@avengers.com"], "from": ["no-reply-sort@cisco.com"]}, "lang": "en", "sizeBytes": 26337, "normalizedHeader": {"subject": ["[EXT] Subject of email"], "message-id": ["1423317795.5042.1557254884493@brms-prd1-25"], "to": ["bruce.banner@avengers.com, supportTT@met-networks.com, \tsopetrov@cisco.com"], "from": ["SORT - PROD "]}, "header": {"subject": ["Subject of email"], "message-id": ["1423317795.5042.1557254884493@brms-prd1-25"], "to": ["bruce.banner@avengers.com, supportTT@met-networks.com, \r\n\tsopetrov@cisco.com"], "from": ["SORT - PROD "]}}, "action_spf": [{"action": "add-header", "rule": "pass", "module": "spf"}, {"action": "continue", "rule": "pass", "module": "spf"}], "final_rule": "pass", "ts": "2019-05-07T12:48:05.173614-0600", "connection": {"tls": {"inbound": {"cipher": "ECDHE-RSA-AES256-GCM-SHA384", "cipherBits": 256, "version": "TLSv1.2"}}, "helo": "alln-app-2.cisco.com", "country": "us", "sid": "2sbeggg6s0", "protocol": "smtp:smtp", "ip": "173.37.142.87", "resolveStatus": "ok", "host": "alln-app-2.cisco.com"}, "pps": {"cid": "agrium_hosted", "agent": "m0046467.ppops.net", "version": "8.11.10.11"}, "envelope": {"rcpts": ["bruce.banner@avengers.com"], "from": "no-reply-sort@cisco.com"}, "action_dkimv": [], "final_module": "pdr", "action_dmarc": [{"action": "continue", "rule": "pass", "module": "dmarc"}], "msgParts": [{"detectedName": "text.html", "labeledName": "text.html", "textExtracted": "U0NBTEFSKDB4N2YzM2U4MTVjZWE4KQ==\n", "detectedSizeBytes": 17794, "labeledMime": "text/html", "sizeDecodedBytes": 17794, "isVirtual": false, "metadata": {}, "labeledCharset": "UTF-8", "sha256": "5029cc915965d0140e2d0ba88c2ae297c278d3a6c1c8b9c228bf515b8b8ab80c", "md5": "cab46e55f172b2b13f9db709cd3bc4db", "detectedExt": "HTML", "disposition": "inline", "isCorrupted": false, "isDeleted": false, "detectedCharset": "UTF-8", "isArchive": false, "dataBase64": "U0NBTEFSKDB4N2YzM2VmZjE3YTAwKQ==\n", "isProtected": false, "structureId": "0", "urls": [{"src": ["urldefense"], "url": "https://www.cisco.com/c/en/us/support/web/tsd-cisco-worldwide-contacts.html", "isRewritten": true}, {"src": ["urldefense"], "url": "http://www.cisco.com", "isRewritten": true}, {"src": ["urldefense"], "url": "https://ibpm.cisco.com/rma/home/?OrderNumber=800127380", "isRewritten": true}, {"src": ["urldefense"], "url": "https://ibpm.cisco.com/rma/home", "isRewritten": true}, {"src": ["urldefense"], "url": "http://supportforums.cisco.com/t5/collaboration-voice-and-video/simplifying-your-cisco-rma-experienc...", "isRewritten": true}], "labeledExt": "html", "isTimedOut": false, "detectedMime": "text/html"}, {"detectedName": "webwb/cisconewlogo.png", "labeledName": "webwb/cisconewlogo.png", "textExtracted": "U0NBTEFSKDB4N2YzM2U4MTAyN2QwKQ==\n", "detectedSizeBytes": 2075, "labeledMime": "image/png", "sizeDecodedBytes": 2075, "isVirtual": false, "metadata": {}, "labeledCharset": "", "sha256": "bb699845aa6f18f0baf339ea3969597abcfdfebb77956efebc5de2d6e1e90c10", "md5": "c6c532f7ebb183c4af68a2d8e320a4ad", "detectedExt": "PNG", "disposition": "attached", "isCorrupted": false, "isDeleted": false, "detectedCharset": "", "isArchive": false, "dataBase64": "U0NBTEFSKDB4N2YzNGRlM2UyMmQ4KQ==\n", "isProtected": false, "structureId": "0", "urls": [], "labeledExt": "png", "isTimedOut": false, "detectedMime": "image/png"}, {"detectedName": "webwb/call_icon.png", "labeledName": "webwb/call_icon.png", "textExtracted": "U0NBTEFSKDB4N2YzM2U4MDE2MzYwKQ==\n", "detectedSizeBytes": 404, "labeledMime": "image/png", "sizeDecodedBytes": 404, "isVirtual": false, "metadata": {}, "labeledCharset": "", "sha256": "d66320e32e99380d33a5cc9212c4216d4ce1c50d34d345b973f4c616a7d7c877", "md5": "dc27600bcf8b5e4cdd882dd4b03eb9ff", "detectedExt": "PNG", "disposition": "attached", "isCorrupted": false, "isDeleted": false, "detectedCharset": "", "isArchive": false, "dataBase64": "U0NBTEFSKDB4N2YzM2U4MTc1NTk4KQ==\n", "isProtected": false, "structureId": "0", "urls": [], "labeledExt": "png", "isTimedOut": false, "detectedMime": "image/png"}], "final_action": "continue", "filter": {"suborgs": {"sender": "0", "rcpts": ["0"]}, "verified": {"rcpts": ["bruce.banner@avengers.com"]}, "qid": "x47IiaKB013302", "quarantine": {"rule": "", "folder": ""}, "modules": {"pdr": {"v2": {"response": "pass"}}, "dkimv": [{"selector": "app", "domain": "cisco.com", "result": "pass"}], "spf": {"domain": "cisco.com", "result": "pass"}, "spam": {"scores": {"classifiers": {"mlx": 0, "impostor": 0, "spam": 0, "adult": 0, "phish": 0, "bulk": 0, "lowpriority": 0, "suspect": 5, "mlxlog": 999, "malware": 0}, "overall": 0}}, "dmarc": {"records": [{"query": "_dmarc.cisco.com", "record": "v=DMARC1; p=quarantine; pct=0; fo=1; ri=3600; rua=mailto:cisco@rua.agari.com; ruf=mailto:cisco@ruf.agari.com"}], "authResults": [{"emailIdentities": {"smtp.mailfrom": "no-reply-sort@cisco.com"}, "result": "pass", "method": "spf"}, {"result": "pass", "propspec": {"header.s": "app", "header.d": "cisco.com"}, "method": "dkim"}, {"emailIdentities": {"header.from": "cisco.com"}, "result": "pass", "method": "dmarc"}], "alignment": [{"from_domain": "cisco.com", "spf": {"identity": "cisco.com", "align": "strict", "identity_org": "cisco.com"}, "dkim": [{"identity": "cisco.com", "align": "strict", "identity_org": "cisco.com"}]}], "srvid": "agrium.com", "filterdResult": "pass"}, "zerohour": {"score": "unknown"}, "urldefense": {"counts": {"unique": 5, "total": 6, "rewritten": 6}, "version": {"engine": "15"}}}, "durationSecs": 0.581787, "routes": ["default_inbound"], "isMsgReinjected": false, "disposition": "continue", "msgSizeBytes": 28953, "isMsgEncrypted": false, "routeDirection": "inbound", "actions": [{"action": "continue", "rule": "pass", "isFinal": true, "module": "pdr"}, {"action": "set-header", "rule": "EXT_add_tag", "module": "access"}, {"action": "continue", "rule": "EXT_add_tag", "module": "access"}, {"action": "add-header", "rule": "pass", "module": "spf"}, {"action": "continue", "rule": "pass", "module": "spf"}, {"action": "add-header", "rule": "clean", "module": "av"}, {"action": "continue", "rule": "clean", "module": "av"}, {"action": "continue", "rule": "pass", "module": "dmarc"}, {"action": "add-header", "rule": "inbound_notspam", "module": "spam"}], "startTime": "2019-05-07T12:48:05.173614-0600"}}

Any leads will be much appreciated!

[DavidHourani · Accepted solution]

Hi @swaguzari,

You're looking for something like this :

index=mail sourcetype=pps_messagelog (final_action=continue OR final_action=discard)
| stats dc(final_action) as nubmer_of_final_action by guid
| where nubmer_of_final_action > 1

This will give you any guid that is seen more than one with both values continue and discard for final_action.

Cheers,
David

[woodcock]

Like this

... | spath
| stats values(final_action) AS final_action dc(final_action) As final_action_count BY guid
| where final_action_count > 1

[dmarling]

How do you want the output to look on your alert? If your goal is to find any guid with >1 final_action this will alert on that:

index=mail sourcetype=pps_messagelog
| stats dc(final_action) as final_actions by guid
| search final_actions>1