Skip to main content

Time Chart: Requests per minute of Top 10 IPs

  • October 28, 2024
  • 2 replies
  • 27 views
Prjct2501
Forum|alt.badge.img+1

Hi all,

I'm coming over from Splunk where this type of query would be pretty trivial, but I'm struggling to make it work in SecOps dashboards;

I've read through this article about pivoting on data to create a basic time chart, but I'm not quite getting the data I want: https://www.googlecloudcommunity.com/gc/Community-Blog/New-to-Google-SecOps-Dashboarding-Using-Pivot-to-Create-a-Time/ba-p/735476

I'd like to see the number of requests per minute of the 10 highest-volume IPs connecting to the network. So far I can easily make a "requests per minute by IP" chart, but it organizes the IPs numerically, (i.e 1.1.1.1, 1.1.1.2, 1.1.1.3, etc.) not by total count, even if I toggle the Count field to display in descending order. 

Anyone have any ideas? Thank you!

2 replies

cmmartin_google
Staff
Forum|alt.badge.img+11

Using YL2 within the 'Preview Dashboards' or 'UDM Stats Search' you can run a YL2 query as follows that to find highest number of events (but you may wish to use another aggregate, e.g., sum of bytes), and then calculate the number of requests (in the below example per second).

 

$sip = principal.ip $dip = target.ip match: $sip, $dip outcome: $event_count = count(metadata.id) $rps = math.round( count(metadata.id) / ( max(metadata.event_timestamp.seconds) - min(metadata.event_timestamp.seconds) ), 2) order: $rps desc limit: 10

 Using Embedded Looker I can't think of a way todo it as an end user as we don't expose the ability to create custom metrics nor use the SQL Runner feature (which allows direct SQL in which case it's easy).

I would suggest if you don't have access to the UDM Stats preview then request that via your Partner or Account team.  The Preview Dashboards feature (which uses UDM Stats, aka YL2) is in private preview and should be going into public preview in the not too distance future.

    Prjct2501
    Forum|alt.badge.img+1
    • Prjct2501
    • Author
    • New Member
    • October 29, 2024

    Using YL2 within the 'Preview Dashboards' or 'UDM Stats Search' you can run a YL2 query as follows that to find highest number of events (but you may wish to use another aggregate, e.g., sum of bytes), and then calculate the number of requests (in the below example per second).

     

    $sip = principal.ip $dip = target.ip match: $sip, $dip outcome: $event_count = count(metadata.id) $rps = math.round( count(metadata.id) / ( max(metadata.event_timestamp.seconds) - min(metadata.event_timestamp.seconds) ), 2) order: $rps desc limit: 10

     Using Embedded Looker I can't think of a way todo it as an end user as we don't expose the ability to create custom metrics nor use the SQL Runner feature (which allows direct SQL in which case it's easy).

    I would suggest if you don't have access to the UDM Stats preview then request that via your Partner or Account team.  The Preview Dashboards feature (which uses UDM Stats, aka YL2) is in private preview and should be going into public preview in the not too distance future.


    Thanks so much, @cmmartin_google ! Yeah, doesn't quite give me the running dashboard view I was hoping for, but the query still gets the data I'm after. 

    As you said, I don't have access to the Preview Dashboards feature (which I'm looking forward to in order to be able to leverage YL2 more for data visualization), but the UDM Stats feature has been very helpful. I'm also eager for the autonomous parsing feature to be globally live. 

    Thank you again for your reply and help!

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

    © 2025 Google. All rights reserved.