Today, I am happy to announce in preview the generative artificial intelligence (generative AI)–powered natural language query generation in AWS CloudTrail Lake, which is a managed data lake for capturing, storing, accessing, and analyzing AWS CloudTrail activity logs to meet compliance, security, and operational needs. You can ask a question using natural language about these activity logs (management and data events) stored in CloudTrail Lake without having the technical expertise to write a SQL query or spend time to decode the exact structure of activity events. For example, you might ask, “Tell me how many database instances are deleted without a snapshot”, and the feature will convert that question to a CloudTrail Lake query, which you can run as-is or modify to get the requested event information. Natural language query generation makes the process of exploration of AWS activity logs simpler.
Now, let me show you how to start using natural language query generation.
Getting started with natural language query generationThe natural language query generator uses generative AI to produce a ready-to-use SQL query from your prompt, which you can then choose to run in the query editor of CloudTrail Lake.
In the AWS CloudTrail console, I choose Query under Lake. The query generator can only generate queries for event data stores that collect CloudTrail management and data events. I choose an event data store for my CloudTrail Lake query from the dropdown list in Event data store. In the Query generator, I enter the following prompt in the Prompt field using natural language:
How many errors were logged during the past month?
Then, I choose Generate query. The following SQL query is automatically generated:
SELECT COUNT(*) AS error_count
FROM 8a6***
WHERE eventtime >= ‘2024-04-21 00:00:00’
AND eventtime <= ‘2024-05-21 23:59:59’
AND (
errorcode IS NOT NULL
OR errormessage IS NOT NULL
)
I choose Run to see the results.
This is interesting, but I want to know more details. I want to see which services had the most errors and why these actions were erroring out. So I enter the following prompt to request additional details:
How many errors were logged during the past month for each service and what was the cause of each error?
I choose Generate query, and the following SQL query is generated:
SELECT eventsource,
errorcode,
errormessage,
COUNT(*) AS errorCount
FROM 8a6***
WHERE eventtime >= ‘2024-04-21 00:00:00’
AND eventtime <= ‘2024-05-21 23:59:59’
AND (
errorcode IS NOT NULL
OR errormessage IS NOT NULL
)
GROUP BY 1,
2,
3
ORDER BY 4 DESC;
I choose Run to see the results.
In the results, I see that my account experiences most number of errors related to Amazon S3, and top errors are related to CORS and object level configuration. I can continue to dig deeper to see more details by asking further questions. But now let me give natural language query generator another instruction. I enter the following prompt in the Prompt field:
What are the top 10 AWS services that I used in the past month? Include event name as well.
I choose Generate query, and the following SQL query is generated. This SQL statement retrieves the field names (eventSource,eventName, COUNT(*) AS event_count), restricts the rows with the date interval of the past month in the WHERE clause, groups the rows by eventSource and eventName, sorts them by the usage count, and limit the result to 10 rows as I requested in a natural language.
SELECT eventSource,
eventName,
COUNT(*) AS event_count
FROM 8a6***
WHERE eventTime >= timestamp ‘2024-04-21 00:00:00’
AND eventTime <= timestamp ‘2024-05-21 23:59:59’
GROUP BY 1,
2
ORDER BY 3 DESC
LIMIT 10;
Again, I choose Run to see the results.
I now have a better understanding of how many errors were logged during the past month, what service the error was for, and what caused the error. You can try asking questions in plain language and run the generated queries over your logs to see how this feature works with your data.
Join the previewNatural language query generation is available in preview in the US East (N. Virginia) Region as part of CloudTrail Lake.
You can use natural language query generation in preview for no additional cost. CloudTrail Lake query charges apply when running the query to generate results. For more information, visit AWS CloudTrail Pricing.
To learn more and get started using natural language query generation, visit AWS CloudTrail Lake User Guide.
— Esra