Something like GROUP BY with Logs Explorer

Question

I am trying to find out details about suspicious traffic on my website which is running on Google Cloud (Google App Engine with Java, to be more specific). One idea is to analyze which IP addresses are sending requests very often. In SQL I would do something like

SELECT 
  protoPayload.ip,
  COUNT(protoPayload.ip) AS `ip_occurrence` 
FROM 
  foo /* TODO replace foo with correct table name */ 
WHERE 
  protoPayload.ip NOT LIKE '66.249.77.%' /* ignore Google bots */
GROUP BY 
  protoPayload.ip
ORDER BY 
  `ip_occurrence` DESC
LIMIT 100

But I have no idea how to do this with Logs Explorer. “Log Analytics” seems to allow such SQL, but requires to use it only on non-production projects.

I also tried to download the logs from Logs Explorer, but there is a limit of 10,000 logs, which is not enough at all.

Is there any easy way?

On the bigger picture, I am trying to get my AdSense account reopened. So far I failed. Maybe the proof I provided, my Google Analytics data, is not strong enough. The field description on the form mentions IP addresses. But in Google Analytics I don't see any IP addresses ...

Labial

Could you tell more details about how you are monitoring your env? It's Legancy? Did solution like this would be helpful or you would need something more specific? – Learn

I am just trying to use that default "Logs Explorer" from Google Cloud. I don't know about the "Legacy log viewer". I don't even understand how to activate it. I don't see any such option. – Labial

I doubt its possible to aggregate logs like Group by, only thinks similar is log engry count. You might also create Feature Request via IssueTracker – Learn

However, did you consider using Log sink to bigquery and try Group By there? Here you have guide how to create sink. Other destination for sink would be Pub/Sub and CloudStorage, however for your scenario BigQuery might be the solution. – Learn

I have no idea about BigQuery or how to use it. – Labial

Answer 1

Log Explorer allows you to create some easy Log Explorer queries for filtering but you won't have any Group By possibility there.

To achieve something similar you can use Sink:

Sinks control how Cloud Logging routes logs. Using sinks, you can route some or all of your logs to supported destinations. Some of the reasons that you might want to control how your logs are routed include the following:

• To store logs that are unlikely to be read but that must be retained for compliance purposes.
• To organize your logs in buckets in a format that is useful to you.
• To use big-data analysis tools on your logs.
• To stream your logs to other applications, other repositories, or third parties.

Supported destinations are:

Cloud Storage: JSON files stored in Cloud Storage buckets.

Pub/Sub: JSON messages delivered to Pub/Sub topics. Supports third-party integrations, such as Splunk, with Logging.

BigQuery: Tables created in BigQuery datasets.

Another Cloud Logging bucket: Log entries held in Cloud Logging log buckets.

For your scenario best would be BigQuery Sink

In the documentation you have a step by step guide on how to Create Sink.

Useful links:

• BigQuery schema for logs
• View logs in sink destinations
• Configure aggregated sinks
• How to export logs from Google Cloud Logging to BigQuery - Youtube video

Learn

Answer 2

If you have the field as a log label, another possibility is to create a log-based metric and group by the label you want.

Beautify