Search code examples
gitlabgitlab-api

Script to get the Gitlab User details like "last_activity_on" into an excel sheet

We have around 10k users in GitLab. We need to prepare an excel sheet that contains the user details like last activity, last sign, created, active or inactive, etc.

These are the details we need to capture in the excel sheet.

Solution

  • To achieve this we will use the Gitlab API https://docs.gitlab.com/ee/api/users.html#for-admins

    GET /users

    In order to get the info you describe you need to use the following script with an admin access token

    #!/bin/bash

echo "Username,Email,State,Created At,Last sign in at, last activity on" > user_activity.csv
curl -s --header "PRIVATE-TOKEN: <admin_access_token>" https://gitlab.example.com/api/v4/users | jq '.[]' | jq "select ((.web_url | contains(\"bot\") | not))" | jq '"\(.username) \(.email) \(.state) \(.created_at) \(.last_sign_in_at) \(.last_activity_on)"' | sed 's/^"\(.*\)"$/\1/' | sed 's/ \{1,\}/,/g' >> user_activity.csv
ssconvert user_activity.csv user_activity.xls

    The output is user_activity.xls