ARTIFACTORY: How to identify unused repositories and users?

Artifactory Query Language (AQL) can be used to retrieve complex information according to the provided search criteria. We usually want to find repositories that are no longer being used by users, either for downloading or for deployment. Additionally, we can determine which users may no olnger be in use by examining when they last logged in to Artifactory. This information can be helpful when evaluating your Artifactory instance before an upgrade, for example, to see what information is still needed, or during regular maintenance procedures.

1. Find the last time an artifact was downloaded from a repository:

This query will search for all the artifacts in the specified repository that have a download date (meaning, the artifact was downloaded at least once), will sort it based on the latest download date and will limit the results to 1 to include the latest artifact. We are also including information on the user who downloaded the file.

items.find({"repo": "<REPO_NAME>", "stat.downloaded":{"$ne":null}}).include("path","name","stat.downloaded","stat.downloaded_by").sort({"$desc" : ["stat.downloaded"]}).limit(1)

Example response:

{
"results" : [ {
  "path" : ".",
  "name" : "abbot-0.12.3.jar",
  "stats" : [ {
    "downloaded" : "2023-05-24T08:07:43.206Z",
    "downloaded_by" : "admin"
  } ]
} ],
"range" : {
  "start_pos" : 0,
  "end_pos" : 1,
  "total" : 1,
  "limit" : 1
}
}

*Additional fields can be included in the output under the “include” section, more information can be found here.

2. Find the last time an item was deployed or modified in a repository:

To get the time that the last time an item was deployed/modified in a repository, we can use the following query:

items.find({"repo": "<REPO_NAME>"}).include("path","name","modified").sort({"$desc" : ["modified"]}).limit(1)

Example result:

{
"results" : [ {
  "path" : ".",
  "name" : "b.txt",
  "modified" : "2023-09-27T13:18:53.923Z"
} ],
"range" : {
  "start_pos" : 0,
  "end_pos" : 1,
  "total" : 1,
  "limit" : 1
}
}

3. Find the last login time of users and filter it based on a date:

To get the list of users that have not logged in to Artifactory since a certain date, we can query the Artifactory DB directly. The date is presented as an epoch timestamp. In example SQL query, we will search for the users that did not login since August 1st 2023:

SELECT username, last_login_time
FROM access_users
WHERE last_login_time < 1696232132;

Or convert the timestamp to a date, for example:

SELECT username, to_timestamp(cast(last_login_time/1000 as bigint))::date
FROM access_users
where to_timestamp(cast(last_login_time/1000 as bigint))::date <= '2023-08-01'

*The syntax may vary depending on the DB type.

It is also possible to get the list of users and their last login time using the Get User List and Get Users Details REST API calls (Note: these API calls are available starting from Artifactory version 7.49.3. For earlier version, the Get Users and Get User Details REST API calls). We may use a simple script to get this information in scripted way for all users. This is an example bash script that can be used as reference:

curl -H 'Authorization: Bearer <TOKEN>' http://ART_HOST/access/api/v2/users | grep "name" | awk '{print $3}' | sed 's/"//g' | sed 's/,//g' > users.txt
for user in $(cat users.txt);
do
timestamp=$(curl -s -H 'Authorization: Bearer <TOKEN>' http://ART_HOST/access/api/v2/users/$user | grep 'last_logged_in' | awk '{print $3}' | sed 's/"//g' | sed 's/,//g')
result="$user: $timestamp"
echo "$result" >> result.txt
done
cat result.txt

The result.txt will look similar to this:

admin: 2023-10-02T08:53:16.069Z
anonymous: 1970-01-01T00:00:00.000Z
test: 2023-10-02T07:30:41.495Z

In case the user has never logged in, the date will show as “1970-01-01T00:00:00.000Z”. The users should be then examined to see if they are indeed inactive.

Note: The data mentioned above serves as suggestions only. Users can change the aforementioned calls and parameters according to their requirements and preferences.