Co-relation Search between two data sources

[swaguzari]

Mighty Splunk people... I'm having a problem creating an alert for following scenario:

Data source 1: index=mail sourcetype=proofpoint_tap_siem (interesting fields = GUID)
Data source 2: index=mail sourcetype=pps_messagelog (interesting fields = guid, final_action)

Basically I want a search which would fire up an alert whenever GUID from 1 matches guid from 2 and has final_action=continue.

Any leads will be much appreciated

[tdwanders]

Koshyk's response should function and will provide more context, but you're not using the data from both searches,  you'd likely see improved performance using a sub-search. This probably doesn't matter unless you have a significant volume of events being evaluated. Search below is untested.

index=mail sourcetype=ppsmessagelog [index=mail sourcetype=proofpointtapsiem final_action=continue | stats values(GUID) as guid]

[koshyk · Accepted solution]

if you can post some sample data, it would have been great

But the idea would be something in terms of:

index=mail (sourcetype=proofpoint_tap_siem OR sourcetype=pps_messagelog)
|rename guid as GUID
| transaction GUID endswith="final_action=continue" keepevicted=true
| search closed_txn=1
| fields _time,GUID,final_action

Or if you want to be more specific, create a key-value for each sourcetype; something like this

index=mail (sourcetype=proofpoint_tap_siem OR sourcetype=pps_messagelog)
|rename guid as GUID
| eval start_event=if(sourcetype=proofpoint_tap_siem, "pair1","na")
| eval end_event=if((sourcetype=pps_messagelog) AND (final_action=continue), "pair2","na")
| transaction GUID startswith="start_event=pair1" endswith="end_event=pair2" keepevicted=true
| search closed_txn=1
| fields _time,GUID,final_action

[swaguzari]

Thanks for the quick turnaround on this. The GUID fields are different in the two data sources: uppercase GUID in Data Source 1, and lowercase guid in Data Source 2. Below is the sample data for both:

Data Source 1 Sample:

{"quarantineFolder": "Phish", "recipient": ["steve.rogers@company.com"], "QID": "2sbcak8mm0-1", "sender": "3be962b290f7d4a361202d6b52be9e9b@rp.mail-tripactions.com", "policyRoutes": ["default_inbound"], "eventTime": "2019-05-07T18:42:39.757Z", "messageID": "<1371062311.475.1557254082679.JavaMail.sbx_user1051@169.254.47.69>", "headerFrom": "Tony Stark ", "impostorScore": 0.0, "replyToAddress": ["3be962b290f7d4a361202d6b52be9e9b@mail-tripactions.com"], "ccAddresses": [], "malwareScore": 0, "xmailer": null, "eventType": "messagesBlocked", "messageTime": "2019-05-07T18:35:21.000Z", "completelyRewritten": false, "messageParts": [{"md5": "c139278b3a51a8712063ff19609d411e", "filename": "text.txt", "sha256": "7b021d9fec5568fb3e67e9be9110fac200689436ca463f44e9d7b207d7cf7bed", "sandboxStatus": null, "disposition": "inline", "contentType": "text/plain", "oContentType": "text/plain"}, {"md5": "1e19fa28a8275bd5af6bce235705f492", "filename": "text.html", "sha256": "15878b8a0f8003d0b8503e33ed78175df92e86ca55fb91369d0cf87fe9c7b127", "sandboxStatus": null, "disposition": "inline", "contentType": "text/html", "oContentType": "text/html"}], "phishScore": 100, "modulesRun": ["access", "smtpsrv", "av", "zerohour", "spf", "dkimv", "sandbox", "spam", "dmarc", "pdr", "urldefense"], "subject": "Subject of Email", "toAddresses": ["steve.rogers@company.com"], "quarantineRule": "module.spam.rule.inbound_phish", "GUID": "WMq0EMGv4NCPoZo6V_UK8U-GsC3eZYvC", "fromAddress": ["3be962b290f7d4a361202d6b52be9e9b@mail-tripactions.com"], "cluster": "agrium_hosted", "senderIP": "192.168.111.222", "headerReplyTo": "Tony Stark ", "spamScore": 100, "threatsInfoMap": [{"campaignID": null, "threatStatus": "active", "threatTime": "2019-05-07T16:06:03.000Z", "threat": "mail-tripactions.com", "threatID": "b8f436f2a79eed6bf6877d4081a8d79aa332e835dcc6caeaf20fe6ae3ce0a8fb", "classification": "phish", "threatUrl": "https://threatinsight.proofpoint.com/43242342dummy-text/threat/email/b8f436f2a79eed6bf6877dummydummy...", "threatType": "url"}], "messageSize": 5670}

Data Source 2 sample:

{"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"}}

[koshyk]

ok, thanks for the sample data. I've updated the above search accordingly to cater for GUID case. Just used a rename

Please upvote/accept if it helped you

[swaguzari]

Done, thanks a ton!!! 🙂