New to Google SecOps: Time, Time, Time, See What’s Become of Me…

[jstoner]

Timestamps are crucial in security operations; we need them to aggregate data, build detections and focus searches. When we start presenting results to analysts and leadership, we need the ability to represent timestamps in a way that is meaningful to the consumer. In the Google Security Operations (SecOps) platform UI, a timestamp is displayed like this, 2024-08-09T23:59:42.787. This is great, the epoch time value is converted into a nice date and time value, but when we present this to our leadership, are we representing August 9th or September 8th?

To help combat this and minimize confusion, we are going to take a look at two timestamp functions. Previously, we discussed timestamp functions that could extract the day of the week, as well as the hour or minute of the timestamp. These functions provide additional options when working with your data for detections, hunts and more!

timestamp.get_timestamp

The timestamp.get_timestamp function is a formatting function. It accepts three arguments. 

timestamp.get_timestamp(unix_seconds, optional timestamp_format, optional timezone)

The first argument is a timestamp value (represented in seconds) from UDM. Fields like metadata.event_timestamp.seconds or metadata.ingested_timestamp.seconds are good examples of fields used in this argument, but it could be any timestamp formatted field. Just make sure you specify .seconds! If you use a timestamp field that ends with .nanos, the syntax is still valid but the results will not contain the expected output. If you get a syntax error, a good first step is to make sure that .seconds or .nanos is appended to the timestamp.

The second and third arguments are both optional. The third argument provides a method to represent the time in a different timezone than GMT, much like other timestamp functions. The second argument is really where the power of this function resides.

If we deploy timestamp.get_timestamp without the second argument, the default output will be in the format of YYYY-MM-DD HH:MM:SS, like this, 2024-09-11 22:12:38.

I’m pretty pleased with that format, but I recognize that might not be the format that everyone wants. Perhaps you want to represent dates in a different format or only want to display a portion of the timestamp. This reference list provides a number of different format elements to choose from. The important point to note is that whichever format you choose, it needs to be enclosed in quotes.

In the search below, we are returning network connection events and using the timestamp.get_timestamp function to format the event timestamp. If we wanted to output the timestamp in our local timezone, we could specify in the third argument "America/Los_Angeles". The second argument is represented as "%c" which is the format element that provides the day of the week, a three character abbreviation for the month followed by the date, time and concludes with the year.

To highlight the differences in date formatting using the timestamp.get_timestamp function, in the outcome section of the search, we’ve outputted the default format, represented by the %F %T format element which gives us the output of YYYY-MM-DD HH:MM:SS and by default is returning GMT.

metadata.event_type = "NETWORK_CONNECTION"
network.sent_bytes > 0
$date = timestamp.get_timestamp(metadata.event_timestamp.seconds, "%c", "America/Los_Angeles")
match:
 $date, principal.ip
outcome:
 $gmt_date = array_distinct(timestamp.get_timestamp(metadata.event_timestamp.seconds, "%F %T"))
order:
 $date desc

In our results, the left column contains the day of the week and the formatted time in Los Angeles, while the right column contains another format for the same date but in GMT time.

You might be thinking, that’s great, there are lots of options for formatting dates and times, but I don’t see one that specifically covers my desired format, what options do I have?

It’s a great question, so let’s leverage our previous search and add one more outcome variable (it’s the one in bold). In this new outcome variable, we are going to format the output where the date comes before the month. For this example, I separated the date, month and year with a forward slash, but this could just as easily be a space, dash or some other character. For the time, I decided to just represent the hour, but use a 12 hour clock and append AM/PM to the output as well. Using the format elements of %l%p I can output 4PM from a timestamp like 2024-09-11 16:12:38.

metadata.event_type = "NETWORK_CONNECTION"
network.sent_bytes > 0
$date = timestamp.get_timestamp(metadata.event_timestamp.seconds, "%c", "America/Los_Angeles")
match:
 $date, principal.ip
outcome:
 $different_date_option = array_distinct(timestamp.get_timestamp(metadata.event_timestamp.seconds, "%d/%m/%Y %l%p"))
 $gmt_date = array_distinct(timestamp.get_timestamp(metadata.event_timestamp.seconds, "%F %T"))
order:
 $date desc

When we run our search, we have the two timestamps from our first search as well as our new column with the timestamp in this new format. We can see they are all the same time, they are just represented in a few different ways.

The timestamp.get_timestamp function is fantastic for taking timestamps and applying formatting to them to represent the date and time in a consistent manner for your organization. Use this function in searches and rules to apply formatting, or even to extract a portion of a timestamp, like an hour or date and hour to group events for statistical purposes!

timestamp.as_unix_seconds

The second function we are going to cover is the timestamp.as_unix_seconds function. This function expects a string value in a date format (more on that in a moment) which will be transformed to an integer value in epoch time that can then be used with mathematical operators, other timestamp functions and more.

Why would we need to do this, who puts a date/time value into a string field? There may be circumstances where a date/time value needs to be parsed in UDM and the meaning of the value doesn’t align with timestamp fields available or perhaps it’s been derived by the source logging system in that format and not in epoch. No matter how it got to that point, we need to figure out how to work with it, which is where this function helps us.

To illustrate how we can use the timestamp.as_unix_seconds function, have GCP network firewall data where the receiveTimestamp is in an extracted field as a string. Notice in the tabular view and event viewer that the format of the value is YYYY-MM-DDTHH:MM:SS.SSS. In fact looking at the event viewer there are more than three digits at the sub second level but that’s ok.

If we want to take this text string and convert it to an epoch value that could be used as a timestamp, I first need to get it into the format element %F %T. We’ve already seen this format with the previous function, that format is YYYY-MM-DD HH:MM:SS.

One method to get our string into this format would be to use other functions like strings.concat and strings.substr to format the date and time portions of the field to a placeholder variable. 

​​$datetime = strings.concat(strings.substr(extracted.fields["receiveTimestamp"],0,10), " ",strings.substr(extracted.fields["receiveTimestamp"],12,8))

Once we do this, our string is in the correct format that can be used with our function to convert to epoch time. To illustrate this, I’ve baked this into a search to highlight how these functions could be used in concert within a broader threat hunt search or detection rule. The filtering statement handles the initial formatting that we just discussed.

metadata.event_type = "NETWORK_CONNECTION"
metadata.log_type = "GCP_FIREWALL"
//extracted.fields["receiveTimestamp"] = "2024-08-12T23:00:06.892489889Z" Example of value as string
$datetime = strings.concat(strings.substr(extracted.fields["receiveTimestamp"],0,10), " ",strings.substr(extracted.fields["receiveTimestamp"],12,8))
outcome:
 $extracted_time_in_text = extracted.fields["receiveTimestamp"]
 $extracted_time_in_text_unformatted = timestamp.as_unix_seconds(extracted.fields["receiveTimestamp"])
 $epoch_timestamp = timestamp.as_unix_seconds($datetime)
 $formatted_time = $datetime
 $formatted_time_epoch_get_timestamp = timestamp.get_timestamp($epoch_timestamp, "%c")
limit: 10

In the outcome section of the search, we have created outcome variables in different formats using the same field before and after functions have been applied to it to illustrate how these functions are used.

• $extracted_time_in_text is the original value of the extracted field in string format
• $extracted_time_in_text_unformatted highlights that we can NOT take the string value and just convert it before we format the string into the %F %T format element. The system doesn’t know what to do with it and returns -1.
• $epoch_timestamp returns the time value as an integer after we’ve converted the string value into the appropriate integer format
• $formatted_time is displaying the properly formatted string that the date/time the timestamp.as_unix_seconds function is expecting
• $formatted_time_epoch_get_timestamp is using the epoch value as an integer and formatting it into another date/time format element for display purposes

In case you are wondering if we take a shortcut and specify an outcome variable like this:

$formatted_time_get_timestamp = timestamp.get_timestamp($datetime, "%c")

I’ll save you the time and tell you that this will trigger an error that looks like this:

compilation error validating query: expect type [int], got type string for "$datetime" line: 10 column: 57-66 : invalid argument

Even though the string is in the correct format, we still need to convert it to epoch format before calling the timestamp.get_timestamp function to format it.

Could we have rolled all of our string and timestamp functions into a single outcome variable? Certainly. In fact here it is.

$formatted_time_epoch_get_timestamp = timestamp.get_timestamp(timestamp.as_unix_seconds(strings.concat(strings.substr(extracted.fields["receiveTimestamp"],0,10), " ",strings.substr(extracted.fields["receiveTimestamp"],12,8))), "%c")

I wanted to break it out for you and show the pieces coming together first. From a testing and troubleshooting perspective, I recommend breaking out functions as we did above so that if parentheses are misplaced, or a function is skipped, it becomes a bit simpler to test and refine first.

I hope these two timestamp functions help with manipulating and formatting dates. Remember these concepts can be applied to searches and rules with more to come!