Skip to main content
Solved

Calculating Time difference from String Time in UDM Search

  • October 10, 2024
  • 3 replies
  • 60 views
mokatsu
Forum|alt.badge.img+6

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.

Best answer by AymanC

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

3 replies

AymanC
Forum|alt.badge.img+14
  • AymanCBronze 5
  • Bronze 5
  • Answer
  • October 10, 2024

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
Forum|alt.badge.img+6
  • mokatsuBronze 5
  • Author
  • Bronze 5
  • October 10, 2024

 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
    Staff
    Forum|alt.badge.img+23
    • jstoner
    • Staff
    • October 10, 2024

    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

     

    Terms & ConditionsCookie settingsAccessibility statement
    Privacy Policy Terms of Service Gen AI Policy Community Guidelines Cookie Settings

    © 2025 Google. All rights reserved.