AQL and JFrog CLI: A Match Made in Heaven

One of the big advantages of running builds using JFrog Artifactory is the exhaustive build information that is created by many sources and used within your organization. However, as your organization continues to grow, efficiently leveraging and managing this amount of data becomes critical. It is very easy to simply use up valuable storage space without really benefiting from what’s there, and without ever getting rid of what’s no longer needed.

This blog post offers some insightful tips on how to efficiently leverage existing Artifactory repository data, maintain high performance, response time and manage storage using Artifactory Query Language (AQL) alongside JFrog CLI.

A set of interconnected domains

Before we dive into an example, let’s take a quick peek at the AQL architecture.

AQL is constructed of a set of interconnected domains, where you can run queries on one domain at a time. This is referred to as the Primary Domain of the query, and it can be on an Item (artifact), Build, Entry, and a Promotion.

You may use fields from other domains as part of your search criteria or to specify fields to display in the output.

For convenience, in addition to specifying a full canonical notation, AQL supports short notation. For example, a query to find items with associated properties named “license” with a value that equals “GPL”, can be written in the following two ways.

Regular notation

items.find({"@artifactory.licenses" : {"$eq" : "GPL"}})

Short notation

items.find({"@artifactory.licenses" : "GPL"})

Advanced cleanup using AQL and JFrog CLI

The following example demonstrates how you can create a meaningful query, that can then be used with JFrog CLI. More specifically, this query targets storage issues that can be solved using efficient cleanup, by identifying the artifacts that are not being used in your Artifactory repositories. JFrog CLI can be used to delete these artifacts from Artifactory by specifying them with an AQL query.

Let’s build the AQL query

We want a query that returns:

• All archive entries named “Artifactory.jar” from any build named “Artifactory,
• with build number 521,
• which are the largest 100 files that were created by the “jenkins-builder” user,
• with the a tar, zip or rpm extension,
• that have never been downloaded,
• have a file size of greater than 100 MB,
• and are tagged with a “qa=approved” property.

Here it is:

items.find(
	{
		"type":"file",
		"created_by":"jenkins-builder",
		"size":{"$gt":"100000000"},
		"stat.downloads":{"$eq":null},
		"@qa":"approved",
		"$or":[
			{"name":{"$match":"*.tar"}},
			{"name":{"$match":"*.zip"}},
			{"name":{"$match":"*.rpm"}}
		]
	}
)
.sort({"$desc":["size","name"]})
.limit(100)

Let’s use the AQL query with JFrog CLI

Once you have specified the exact details of your query, you can embed it in a JFrog CLI command, such as Delete Files. This command will remove these unnecessary files from Artifactory.

Before we go ahead and delete the artifacts, it’s always best practice to first perform a dry run and simulate the execution. This functionality is available with the “–dry-run” command option, or you can even safely use the CLI Search command.

JFrog CLI also works with JFrog Mission Control, JFrog Bintrayand an integration is also planned for JFrog Xray.

Managing the past, present and the future of artifacts

That’s the essence of AQL. By providing such an extremely flexible language that lets you specify any number of search criteria, combined in any logical configuration, with any set of filters and displaying any set of output fields, not only can we satisfy any request we have ever received, we can also satisfy any future request that nobody has dreamed of yet. Paired with JFrog CLI that provides a simple interface that automates access to JFrog products, this is simply a match made in heaven that makes your scripts even more efficient and reliable!

Learn more about JFrog CLI

Learn more about AQL