View "last modified" project info using gcloud CLI/terminal

[mi1ez]

I am looking for a way to view "last modifed" or the date of the latest activity log, commit, modify, etc. on a project using the CLI/terminal so I can get a rough idea of what projects are(n't) in use for an account cleanup.

Would anyone have a recommendation of how to do this programmatically, or how I can find similar data.

[christianpaula]

Hi @mi1ez,

Welcome to Google Cloud Community!

The gcloud command-line tool, which is part of the Google Cloud SDK, allows you to interact with various Google Cloud Platform services, including Google Cloud Storage, which is where most of the data for a project would be stored.

You can use the gsutil command, which is part of the Cloud SDK and allows you to interact with Google Cloud Storage, to view the last modified date of all the objects in a bucket. This can give you a rough idea of the last time a project was accessed. Here's an example command that will display the last modified date of all objects in a bucket called "my-project-bucket":

gsutil ls -l gs://my-project-bucket/**

You can also add -h option in ls command to human-readable format.

gsutil ls -l -h gs://my-project-bucket/**

Also, you can use Google Cloud Logging and stackdriver to get the Activity logs for your project.

gcloud logging read 'resource.type="project" AND jsonPayload.methodName="storage.objects.update"' --project=my-project-id --format='table(jsonPayload.bucket, jsonPayload.object, timestamp)'

This will show you a table of logs with the bucket, object, and timestamp of the update.

You can also use this to query other logs such as Compute Engine, Cloud SQL and many more.

Keep in mind that data may be deleted from the bucket, so it can give you an idea last accessed but not 100% accurate.

Thank you

[mi1ez]

Just looking at a project, I can see the last modifieds on storage buckets listed as 2016 and 2017 but IAM changes in 2022 so unfortunately that won't work, pointing me towards those logs are certainly a help.

The flollowing seems to get me something of some use, just not in a timely manner. I calculate this will take around 200hrs to complete in fact. About 2 mins per iteration and 6000+ projects.

#!/bin/bash
echo "Project,Folder,Owners,Last Log Date,Last Log Data"
for i in $(gcloud projects list --format="value(projectId)" | grep -v sys-)
do 
  PARENT=$(gcloud projects describe $i --format="value(parent.id)")
  OWNERS=$(gcloud projects get-iam-policy $i --flatten="bindings[].members" --filter="bindings.role=roles/owner" --format="value(bindings.members)")
  LASTLOG=$(gcloud logging read "" --project=$i --freshness=1y --limit=1 --format="value(protoPayload.methodName)")
  LOGLOGDATE=$(gcloud logging read "" --project=$i --freshness=1y --limit=1 --format="value(receiveTimestamp)")
  OWNER=$(echo "$OWNERS" | tr '\n' ' ')
  echo "$i,$PARENT,$OWNER,$LASTLOGDATE,$LASTLOG"
done

[kolban]

Howdy ... I think I'm tempted to ask what one means by "last accessed" for a project?  What is going through my mind is the following:

A Google Cloud Project is a "container" for Google Cloud resources.  There are MANY MANY different types of resources such as Compute Engines, Cloud Functions, Cloud Run, Cloud Storage, VPC networking,  AI tools, databases and many, many more.  A developer configures these resources within a project and they then get "consumed".  For example, Compute Engines run code, databases process queries, AI runs predictions and so on.   Now we are left with the puzzle ... what does it "mean" to "last access" a project?  It could mean the last time a resource was added, deleted or modified in the project.  It could mean the last time a query was run on a database.  It could mean the last time a Cloud Storage bucket was read or written ... and so on.   Maybe we can pin down a little more what it is we mean by "last accessed"?

[mi1ez]

Last modified was more what I was referring to, I'll update the question.

[mi1ez]

Although now I'm thinking about it, if a project is receiving traffic, files, processing data, etc; I should probably know about that as well as last modified.

[kolban]

If we consider a Google Cloud project as a "container" of resources and we define "last modified" as a change being made to the configuration of that project (change to the container) then we might be able to work with Admin Activity audit logs.  These are log records that can not be disabled and are always written.  The docs for them read:

Admin Activity audit logs contain log entries for API calls or other actions that modify the configuration or metadata of resources. For example, these logs record when users create VM instances or change Identity and Access Management permissions.

The puzzle would then be one of running a query to determine which projects have records associated with these logs.  While it won't help for historic data, I'm tempted to suggest that you look into writing these logs into a BigQuery table.  Cloud Logging can automatically direct these logs into such a "sink" on your behalf.  The value of writing them into BigQuery is that you can then formulate a SQL query to give you  the answer to your analytics question.  Something loosely along the line of:

select distinct project_id from mylog_table where insert_date > "2021-01-01"

This would give you near real time answers to your analytics question and can be run from the command line using the bq command.

[mi1ez]

Starting from scratch, that certainly sounds like the thing to do, but unfortunately our whole org has had access to create projects for some time, and a lot of people have had a play. Now it falls to me to clean it up, or poke the right people to clean up after themselves.

[kolban]

Howdy again ... while I haven't tried it myself ... could this article be of value to us?

https://cloud.google.com/blog/products/identity-security/google-cloud-launches-unattended-project-re...

See also:

https://cloud.google.com/recommender/docs/unattended-project-recommender

From a quick glance perspective ... this looks VERY encouraging!!  Thank YOU for causing me to go look for such a thing 🙂