Hi @mokatsu,
Does the below satisfy your requirement - I don't have access to any of the data you have in your instance, or any replica data.
metadata.log_type = "ABNORMAL_SECURITY" metadata.event_type = "EMAIL_TRANSACTION"
// replace
// format time string correctly
$Received_Time_Formatted = re.replace(re.replace(additional.fields["mailReceivedTime"], "T", " "), "Z", "")
$Remediated_Time_Formatted = re.replace(re.replace(additional.fields["mailRemediationTimestamp"], "T", " "), "Z", "")
match:
security_result.threat_name
outcome:
$c = count(security_result.threat_name)
$Diffavg = array_distinct(timestamp.diff(timestamp.as_unix_seconds($Received_Time_Formatted), timestamp.as_unix_seconds($Remediated_Time_Formatted), "SECOND"))
In terms of why the error is occurring, this great blogspot[1] by thatsiemguy explains that this issue is something observed
Hope this helps!
[1] - https://medium.com/@thatsiemguy/aggregate-queries-in-udm-search-1b885c8c27d5
Kind Regards,
Ayman C
I did find my issue, will post here incase someone comes across it.
It seems that assigning variable to variable doesn't act like I expected, fixing the query to
#### From
metadata.log_type = "<LogSource>" metadata.event_type = "EMAIL_TRANSACTION"
// replace
// format time string correctly
$recieved_time_s = re.replace(re.replace(additional.fields["mailReceivedTime"], "T", " "), "Z", "")
$remediated_time_s = re.replace(re.replace(additional.fields["mailRemediationTimestamp"], "T", " "), "Z", "")
$received_time = timestamp.as_unix_seconds($recieved_time_s)
$remediated_time = timestamp.as_unix_seconds($remediated_time_s)
match:
security_result.threat_name
outcome:
$c = count(security_result.threat_name)
$dif_avg = array_distinct($remediated_time - $received_time)
### To
metadata.log_type = "<LogSource>" metadata.event_type = "EMAIL_TRANSACTION"
// replace
// format time string correctly
$received_time = math.round(timestamp.as_unix_seconds(re.replace(re.replace(additional.fields["mailReceivedTime"], "T", " "), "Z", "")), 4)
$remediated_time = math.round(timestamp.as_unix_seconds(re.replace(re.replace(additional.fields["mailReceivedTime"], "T", " "), "Z", "")), 4)
match:
security_result.threat_name
outcome:
$c = count(security_result.threat_name)
$dif_avg = array($remediated_time - $received_time)
Based on what you are describing here and based on what I found during my testing of the as_unix_seconds function, I was wondering if something like this might work because the expected format for the conversion to unix seconds is 2024-08-12 23:00:06
$datetime = strings.concat(strings.substr(extracted.fields["receiveTimestamp"],0,10), " ",strings.substr(extracted.fields["receiveTimestamp"],12,8))
outcome:
The reason you are getting that non-descriptive error I suspect is because you are going placeholder variable to placeholder variable in the filtering statment to massage the data a second time.
To alleviate that, nest the functions in a single statement in the filtering statement
$datetime = timestamp.as_unix_seconds(strings.concat(strings.substr(extracted.fields["receiveTimestamp"],0,10), " ",strings.substr(extracted.fields["receiveTimestamp"],12,8)))
Alternatively, depending when you need the value, you could also use the as_unix_seconds function in the outcome section instead
$outcome_variable = array_distinct(timestamp.as_unix_seconds($datetime))
I posted a blog last week about the as_unix_seconds and get_timestamp functions and included it below for reference.
https://www.googlecloudcommunity.com/gc/Community-Blog/New-to-Google-SecOps-Time-Time-Time-See-What-s-Become-of-Me/ba-p/813704
