Latest timestamp by Status
I am trying to figure out a way to monitor the status of my VPN tunnels on my firewall and I I want to create a query that shows me the most recent status by VPN:
Ex. Data:
IP_Sec_Tunnel note _MessageTime
Tunnel 1 Tunnel Up 3:00PM
Tunnel 1 Tunnel Down 3:15PM
Tunnel 2 Tunnel Down 2:00PM
Tunnel 2 Tunnel Up 2:30PM
I want the latest status by the _messageTime to be returned by IP_Sec_Tunnel
Desired result:
Tunnel 1 Tunnel Down _MessageTime
Tunnel 2 Tunnel Up _MessageTime
This is what I have so far, but it gives me the latest message time for each status:
_source="Firewall" and _collector="xxxxxxxxx"
| parse "(*)" as Ip_Sec_Tunnel
| parse "note=\"*." as note
| where msg = "IPsec Tunnel status changed"
| count, max(_messagetime) as last_login_time by note, Ip_Sec_Tunnel
| toLong(last_login_time)
| formatDate(fromMillis(last_login_time), "yyyy-MM-dd HH:mm:ss") as last_login_time
| parse "(*)" as Ip_Sec_Tunnel
| parse "note=\"*." as note
| where msg = "IPsec Tunnel status changed"
| count, max(_messagetime) as last_login_time by note, Ip_Sec_Tunnel
| toLong(last_login_time)
| formatDate(fromMillis(last_login_time), "yyyy-MM-dd HH:mm:ss") as last_login_time
Please sign in to leave a comment.
Comments
0 comments