Calculating Time difference from String Time in UDM Search

[mokatsu]

Hi, 

I am trying to calculate the time difference from a fields that has a timestamp in string format (%Y-%m-%dT%H:%M:%S%Z ("2024-10-10T13:51:32Z")), the search does not seem be get any compilation error but when the search and ran. it receives an error after running. "Error: Search has encountered an error and could not load data. Please try again, and contact support if this error continues." 

This seems to happen when I try to use the timestamp.as_unix_seconds()

Was wondering if anyone came across a similar issue.

Here is the search:

metadata.log_type = "ABNORMAL_SECURITY" 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)

Yes I am aware about the regex replace commands, but the timestamp command doesn't allow me to provide a custom format.

[AymanC · Accepted solution]

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

[AymanC · Accepted solution]

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

[mokatsu]

 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)

[jstoner · Accepted solution]

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-...