I have a log based metric that tracks every time a certain event makes it to Windows Event Log. I set it up with a label that captures the time from the log's payload.
I want to create a dashboard that will show the machine name and only the most recent time stamp. I'm having trouble grouping by machine name with the max time stamp for each machine using MQL. Any suggestions?
I've had a lot of failed attempts, but here's a basic query to show the information I need
fetch gce_instance | metric 'logging.googleapis.com/user/log_based_event' | align rate(1m) | every 1m | group_by [metric.log_based_event_time, resource.project_id, metadata.system.name: metadata.system_labels.name]
I've played with trying to get the max of the time stamp, but I'll get conflicting errors that that information is a string (when I want to do a max on the date) or I get errors that it's a date (when I try to convert the string to a date).
Based on the code presented, you can try to embed the following to achieve your end goal:
-In this line you replace 'start_time' with your desired start time
“filter timestamp >= 'start_time'”
-Here the data is grouped by machine name and system labels then calculates the maximum timestamp for each group.
“group_by [metadata.system.name, metadata.system_labels.name], max_val=max(metric.log_based_event_time)”
-This groups the data again by machine name and calculates the maximum timestamp for each machine. Put in mind that the max() function is used for aggregating timestamps and you might need to adjust this based on the format of your timestamp. In addition, ensure that your timestamp is in a format that allows mathematical operations. This assumes that the timestamp is in a format that is in numerical form. Please note that in case your timestamp is in string format, you need to convert it first to numeric timestamp.
“group_by [metadata.system.name], max_timestamp=max(max_val)”
With those in mind, your code should look like this:
fetch gce_instance
| metric 'logging.googleapis.com/user/log_based_event'
| filter timestamp >= 'start_time'
| align rate(1m)
| every 1m
| group_by [metadata.system.name, metadata.system_labels.name], max_val=max(metric.log_based_event_time)
| group_by [metadata.system.name], max_timestamp=max(max_val)
Just remember to change 'start_time' with the actual start time for your query. Adjust the range based on your requirements.Attached are documentations to further explore your options with cloud monitoring and logging.[1][2]
[1] https://cloud.google.com/monitoring/custom-metrics/creating-metrics
[2] https://cloud.google.com/logging/docs/logs-based-metrics