Assets AQL - creating a filter for license updates

[Ron Aisen]

Hi There :)

I'd like to create an AQL filter for Assets dashboard I have in Jira for license renewal

I have a table with license objects with 2 relevant columns name and anual renewal dates I'd like to create an AQL filter based on those 2 columns, how would the syntax go?

[Pablo Brincat · Accepted solution]

Hi Ron,

Creating an AQL (Asset Query Language) filter for your Jira Assets dashboard to track license renewals based on the license name and annual renewal dates is a practical way to stay ahead of renewals. Here’s a basic structure for your AQL query:

Basic AQL Structure

Assuming you have a table of licenses in Jira Assets with fields for the license name and annual renewal dates, your AQL query might look something like this:

type = "License" AND "Name" IS NOT EMPTY AND "Annual Renewal Date" >= startOfMonth() AND "Annual Renewal Date" <= endOfMonth()

Breaking Down the Query

1. Type Filter: type = "License" ensures that the query only looks at assets of the ‘License’ type.

2. Name Check: "Name" IS NOT EMPTY filters out any records where the license name is not entered.

3. Date Range for Renewals: "Annual Renewal Date" >= startOfMonth() AND "Annual Renewal Date" <= endOfMonth() sets the filter to show licenses whose renewal dates fall within the current month. You can adjust the timeframe as needed. For example, if you want to see renewals for the next month or a specific date range, you can modify the startOfMonth() and endOfMonth() functions accordingly.

Adjusting the Query

• If you want to see renewals for the next 30 days regardless of the month, you might use something like "Annual Renewal Date" >= now() AND "Annual Renewal Date" <= now() + 30d.

• If you have specific names or categories of licenses to filter, you can add conditions like "Name" = "Specific License Name".

Additional Considerations

• Custom Fields: Ensure that “Name” and “Annual Renewal Date” match the exact field names in your Jira Assets setup.

• Time Zone Awareness: Be mindful of time zones if your Jira instance and users span multiple time zones.

• Test the Query: Always test your AQL query to ensure it returns the expected results, especially if you’re using custom fields or complex date calculations.

• Jira Assets Documentation: For more complex queries or specific use cases, refer to the Jira Assets AQL documentation for additional functions and syntax.

This query should help you create an effective filter for your license renewal dashboard in Jira, allowing you to stay proactive with your license management! 🚀📊🗓️

[Ron Aisen]

Thanks for the Help this was very helpful! you've managed to solve my query! much appreciated :)