How to list all folders with more then 10 artifacts in artifactory repo using CURL?

Question

I have an artifactory issue. I need to implement a retention policy, to keep just 10 artifacts per folder. and I need help with that.

This is my initial curl command, to get all folders with the count > 10,

execute_aql() {
    # Execute AQL query to find folders with more than 10 artifacts
    curl_response=$(curl -s -u "admin:$API_KEY" -X POST "$ARTIFACTORY_URL/artifactory/api/search/aql" -H "Content-Type: text/plain" -d 'items.find({"type": "folder", "repo": "'"$REPO_NAME"'"}).include("name","children.items.name","children.items.@count").filter(function(item){ return item.children.items.@count > 10 })')

    # Print the response
    echo "$curl_response"
}

but it isnt working as expected, in fact it fails:

+ ./retPolicy.sh
Failed to parse query: items.find({"type": "folder", "repo": "HLRP_GENERIC_DEV"}).include("name","children.items.name","children.items.@count").filter(function(item){ return item.children.items.@count > 10 }), it looks like there is syntax error near the following sub-query: children.items.name","children.items.@count").filter(function(item){ return item.children.items.@count > 10 })

And I dont know why!

I wanted to add a sort algorithm on top of it, to sort by time, and delete the oldest artifacts, but I didnt come that far!

Hopefully you guys can help me out!

Thanks for reading this!

Answer 1

AQL is very powerful, but it is not JavaScript and does not support the syntax you tried to use.

To achieve your goal you can use AQL in the way described below. Please note that -

1. It puts some load on Artifactory so it is better to throttle the execution
2. Instead of using curl you can use the JFrog CLI which makes it simpler and also provides ability to delete files using AQL (see notes below)

Steps:

1. Find folders
2. Find files in the folder
3. Delete files

Step 1 - Find folders

Given a repository, find all the folders in the repository:

items.find({"repo":"my-repo","type":"folder"}).include("repo","path","name")

Results:

{
  "results" : [ {
      "repo" : "my-repo",
      "path" : "some/path",
      "name" : "foo"
    }, ... 
  ],
  "range" : { ... }
}

Notes -

• It is better to avoid searching for all folders and limit the search by path or path pattern match (i.e. starts with).
• You can search for folders in multiple repositories (e.g. using "repo":{"$match":"*"}), but in general it is better to focus the search with exact match (i.e. equal).

Step 2 - Find files to remove from each folder

For each folder from step 1 - find the files in the folder, sort descending by created time (can be sorted by modified instead).

items.find({"repo":"my-repo","path":"some/path/foo","type":"file"}).include("repo","path","name","created").sort({"$desc":["created"]})

Results:

{
  "results" : [ {
      "repo" : "my-repo",
      "path" : "some/path/foo",
      "name" : "bar.txt",
      "created" : "2023-09-04T10:40:58.737Z"
    }, {
      "repo" : "my-repo",
      "path" : "some/path/foo",
      "name" : "baz.txt",
      "created" : "2023-09-04T10:35:43.274Z"
    }, ... ],
  "range" : { ... }
}

Step 3 - Delete files

For each folder - keep the first 10 files, every other file can be deleted.

Notes on using JFrog CLI

Using the JFrog CLI you can change the steps above to -

1. Do the same using the cli
2. Search for the files sorted by time, but limit to the first 10 results
3. Use AQL to delete all files (in the folder) which were created before the 10th file

See blog on Efficient mass artifact cleanup method using JFrog CLI and AQL