Here's a sample query I have:
metadata.event_type = "EMAIL_TRANSACTION" and network.direction = "INBOUND" " $url = about.url $url in %IOC_List match: $url outcome: $Total_Email_Count = count_distinct(metadata.id) $Total_Email_Allowed=sum(if(security_result.action!="BLOCK", 1,0)) $Total_Email_Blocked=sum(if(security_result.action="BLOCK", 1,0)) order: $Total_Email_Count desc
My goal is to check if a url came up in inbound emails and return counts by a simplified version set of variables for security_result.action - if not explicitly blocked, count it as "Allowed" (along with a total count of emails). My problem is that about.url is an array of values, where the same url can come up several times - meaning my total email count might be 10, but my total allowed is 20, because each email is counted twice under $Total_Email_Allowed because the url is in about.url twice. Any suggestions?
Stats Query - How to Avoid Doublecounting
+8
+12Can you give a sanitized example for the highlighted UDM event(s) you are tracking and the expected outcome counts ?
I can think of a parsing-based alternative to create an additional field that has the deduplicated joins between URLs and action ( url1-block, url2-allow, url1-allow, ..etc) having only unique url-action pairs or even the required counts from a loop in the parser, but I would rather see the raw event fields first against the desired counts.
+8
"verified": {
"rcpts": [
"user@example.com"
]
},
"durationSecs": 2.500006,
"routeDirection": "inbound",
"msgSizeBytes": 178116,
"suborgs": {
"rcpts": [
"0"
],
"sender": "0"
},
"delivered": {
"rcpts": [
"user@example.com"
]
},
"routes": [
"default_inbound"
],
"disposition": "continue",
"qid": "56EE5HjA010374",
"aliasedRcpts": [
{
"alias": "user@example.com",
"primary": "First.Last@example.com"
}
]
},
"ts": "2025-07-14T14:37:09.193502+0000",
"msgParts": [
{
"isVirtual": false,
"sizeDecodedBytes": 874,
"labeledMime": "text/plain",
"isProtected": false,
"detectedExt": "TXT",
"detectedCharset": "utf-8",
"structureId": "0:0",
"dataBase64": "U0NBTEFSKDB4N2Y2ZTg4NTczODg4KQ==\n",
"isCorrupted": false,
"labeledCharset": "UTF-8",
"labeledName": "text.txt",
"sha256": "543966fc51e2ff09d3eb588db6d346bfcf0d3a660db014e3d06670ec81c6bb2f",
"labeledExt": "txt",
"md5": "f9e77bb127ea11b4449893f531283a44",
"detectedSizeBytes": 874,
"textExtracted": "U0NBTEFSKDB4N2Y2ZTk0Zjc5Yjg4KQ==\n",
"isDeleted": false,
"metadata": {},
"isArchive": false,
"isTimedOut": false,
"sandboxStatus": "NOT_SUPPORTED",
"detectedName": "text.txt",
"urls": [
{
"isRewritten": true,
"url": "http://www.ybs.co.uk",
"src": [
"filter",
"urldefense"
]
}
],
"disposition": "inline",
"detectedMime": "text/plain"
},
{
"dataBase64": "U0NBTEFSKDB4N2Y3ZjAwYTY4ZmQ4KQ==\n",
"isCorrupted": false,
"labeledCharset": "UTF-8",
"sha256": "dedcb35d2570cf630f0d60ba5e90db30b8dce32a046da521a341959449f5bd00",
"labeledExt": "html",
"labeledName": "text.html",
"md5": "fdea06b5227549ebc8fbd963edce378",
"sizeDecodedBytes": 8919,
"isVirtual": false,
"labeledMime": "text/html",
"isProtected": false,
"detectedExt": "HTML",
"structureId": "0:1",
"detectedCharset": "utf-8",
"isTimedOut": false,
"isArchive": false,
"sandboxStatus": "NOT_SUPPORTED",
"detectedName": "text.html",
"urls": [
{
"src": [
"filter",
"urldefense"
],
"url": "http://www.ybs.co.uk",
"isRewritten": true
},
{
"url": "https://vector.my.salesforce.com/",
"src": [
"filter"
]
},
{
"src": [
"filter",
"urldefense"
],
"url": "https://console.cloud.google.com/support",
"isRewritten": true
},
{
"url": "http://services.google.com/gcp_newsletter_email_cloud_footer_logo.png",
"src": [
"filter"
]
},
{
"url": "https://vector.my.salesforce.com/",
"src": [
"filter"
]
}
],
"detectedMime": "text/html",
"disposition": "inline",
"detectedSizeBytes": 8919,
"textExtracted": "U0NBTEFSKDB4N2Y2ZjE0OTdjNzY4KQ==\n",
"isDeleted": false,
"metadata": {}
},
{
"detectedCharset": "",
"structureId": "1",
"detectedExt": "PNG",
"isProtected": false,
"isVirtual": false,
"sizeDecodedBytes": 113081,
"labeledMime": "image/png",
"md5": "465fa99a144c877ae5c515324dfb6a64",
"labeledName": "image.png",
"sha256": "02b576e4372ea626567e6ee0872929b4ccef6dac343730e71d3faaba79203aaa",
"labeledExt": "png",
"isCorrupted": false,
"labeledCharset": "",
"dataBase64": "U0NBTEFSKDB4N2Y2ZTg1OGUyMjcwKQ==\n",
"metadata": {
"imageheight": 800,
"imagebitsperpixel": 24,
"height": 800,
"imagewidth": 1919,
"bits per pixel": 24,
"width": 1919
},
"isDeleted": false,
"detectedSizeBytes": 113081,
"textExtracted": "U0NBTEFSKDB4N2Y3ZTg1YTI0ODI5KQ==\n",
"disposition": "attached",
"detectedMime": "image/png",
"detectedName": "image.png",
"urls": [
{
"url": "http://www.ybs.co.uk",
"src": [
"cvtd"
]
}
],
"isArchive": false,
"isTimedOut": false,
"sandboxStatus": "NOT_SUPPORTED"
}
],
"msg": {
"normalizedHeader": {
"message-id": [
"message@example.net"
],
"from": [
"Google Cloud Support <cloudsupport@google.com>"
],
"to": [
"\"user@example.com\" <user@example.com>"
],
"return-path": [
"<cloudsupport@google.com>",
"<cloudsupport@google.com>"
],
"subject": [
"[External] Google Cloud Support 61233491: How to run stats query without doublecountung"
]
},
"sizeBytes": 169930,
"header": {
"message-id": [
"<example@first.net>"
],
"from": [
"Google Cloud Support <cloudsupport@google.com>"
],
"to": [
"\"user@example.com\" <user@example.com>"
],
"return-path": [
"<cloudsupport@google.com>",
"<cloudsupport@google.com>"
],
"subject": [
"Google Cloud Support 61233491: How to run stats query without\r\n doublecountung"
]
},
"lang": "en",
"parsedAddresses": {
"from": [
"cloudsupport@google.com"
],
"to": [
"user@example.com"
],
"fromDisplayNames": [
"Google Cloud Support"
]
}
},
"envelope": {
"from": "cloudsupport@google.com",
"rcpts": [
"user@example.com"
]
}
}
Here’s sanitised example of one event.
+8In the example provided, the raw data gets parsed into the Proofpoint POD type. If I search by about.url I get 3 events returned in the UI in triplicate. If I search by metadata.product_log_id, I get 1. I cant search by both at the same time.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.