How to insert value at start of column in csv file using shell script

user_4003942 0
Viewed 170

I have the following code to get API data and store it in a csv file:

for projectid in `gcloud scc assets list <someorgID>
   --filter="security_center_properties.resource_type=\"google.cloud.resourcemanager.Project\""
   -- format="get(asset["securityCenterProperties"]["resourceDisplayName"])"`
   
   do
       gcloud recommender recommendations list \
       --location=global \
       --recommender=google.iam.policy.Recommender \
       --project=$projectid \
       --format="csv[no-heading](content["overview"], content["operationGroups"])" >> temp_iam.csv  done

This will get result such as:

cloudresourcemanager.googleapis.com,    remove
cloudresourcemanager.googleapis.com,    add

I want to add the value of $projectid to the first place as:

projectid1, cloudresourcemanager.googleapis.com, remove
projectid2, cloudresourcemanager.googleapis.com, add

How I can achieve this?

gcloud csv shell
2 Answers

You could ask awk to insert the loop variable; here's a simplified loop to demonstrate:

for projectid in projectid1 projectid2
do
  gcloud ... | awk -v project="$projectid" '{ $1=project ", " $1; print;}'
done

This pipes the output of gcloud ... to awk; awk is given a variable named "project" which is set to the value of the loop variable $projectid. Then on each line of awk's input (gcloud's output), we replace the first field with a concatenation of that project variable, a command and a space, then value of the first field -- essentially prefixing the projectid value as the new first value of the CSV output.

The new script would be:

for projectid in `gcloud scc assets list <someorgID>
   --filter="security_center_properties.resource_type=\"google.cloud.resourcemanager.Project\""
   -- format="get(asset["securityCenterProperties"]["resourceDisplayName"])"`
   do
       gcloud recommender recommendations list \
       --location=global \
       --recommender=google.iam.policy.Recommender \
       --project=$projectid \
       --format="csv[no-heading](content["overview"], content["operationGroups"])" | \
       awk -v project="$projectid" '{ $1=project ", " $1; print;}' >> temp_iam.csv
   done
user_49575080

GCP APIs contain the resource name in the response, so part of that response contains the project number.

Specifically, gcloud recommender recommendations list is calling the projects.locations.recommenders.recommendations.list method of the Recommender API, which responds with a Recommendation resource, and this is received by the gcloud tool, which means we already have the project in the response.

To know how to properly print that information, we can look at gcloud topic projections, and we can see a few transforms that could be applicable here.

If you're interested in the project number, you can select the second segment (index 1) of the name resource for the recommendation:

gcloud recommender recommendations list --format="value(name.segment(1))" --location=global --recommender=google.iam.policy.Recommender

To include this in your current command that prints a CSV, you can use:

gcloud recommender recommendations list \
  --location=global \
  --recommender=google.iam.policy.Recommender \
  --project=$projectid \
  --format="csv[no-heading](name.segment(1),content["overview"], content["operationGroups"])" >> temp_iam.csv
user_13088830
Related