Problem Statement: In the app I have 3 type of grouped queries: search, update, and create. The search is one query, the Update is 4 queries, and Create is 4 queries.
When user clicks 'submit to stage' button, updates or create, queries 1-2 are ran and pushes configs to stage envi.
User checks stage envi, if good, then user push's 'PROD' button and queries 2-4, are ran.
With the audit logs I would like to create a table that has the columns: user id, time, and config updated/created
I tried manipulating this SQL query to pull the info from above but am struggling:
select
u.email, u."userName", a."actionType", a.metadata
from
audit_trail_events a
join users u on a."userId" = u.id
where a."actionType" = 'CREATE_GROUP';
Is there a way to pull the requested information from the audit logs and if so, what would the SQL query look like?
Hey @Ian_Stack — Alex here from the Retool team. Just to confirm, are you self-hosting Retool or are you on Retool cloud? As of writing, you can only query the audit logs with SQL if you are self-hosted Retool.
Otherwise, if you are on the Business Plan, you can access the audit logs from https://yoursubdomain.retool.com/audit and use the UI to filter logs to your page & query name and download them as a CSV.
If you are self-hosted, let me know and I can help with the SQL query!
I am on self-hosted retool. We have a retool usage app that utilized audit logs. Also in the audit logs I see the UI with pagename, query, user, and date range. However I want to add more filters. I am open to any ideas for a SQL query though!
Hey @Ian_Stack, great here's a query that you can modify as needed to query the logs:
A few notes on the where clause:
Unless you are using Spaces, you don't need to add the organizationId = 18511 in the where clause. If you are using a single space, all records will have the same value for the organizationId.
Make sure to update this query to add your app name (this is optional but will disambiguate if you have queries with the same name in different apps).
Update the value in queryName = ANY() to be a comma-separated list of queries that you want to target (using the query name from the app). It sounds like you might need 2 separate queries targeting your different list of queries in the app?
I also added a time range in the where clause to reduce the number of records returned, feel free to adjust or remove as necessary.
SELECT
u.email,
u."userName",
a."actionType",
a.metadata
FROM
audit_trail_events a
JOIN
users u on a."userId" = u.id
where
a."pageName" = 'Your App name'
and a."actionType" = 'QUERY_RUN'
and a."queryName" = ANY('query1','query2')
and a."createdAt" > '2024-07-01T00:00:00.000Z'
and a."createdAt" < '2024-07-10T00:00:00.000Z'
order by
a."createdAt" asc
Hello there, I tried using the SQL query you created however I recieved a 'query error failed' message: 'syntax error at or near ",""
I updated the SQL query you gave to this:
SELECT
u.email,
u."userName",
a."actionType",
a.metadata
FROM
audit_trail_events a
JOIN
users u on a."userId" = u.id
where
a."pageName" = 'Next Gen Contact Mock up'
and a."actionType" = 'QUERY_RUN'
and a."queryName" = ANY('create_contactflow_iconbar_1','Update_contactflow_iconbar')
and a."createdAt" > '2024-07-01T00:00:00.000Z'
and a."createdAt" < '2024-07-10T00:00:00.000Z'
order by
a."createdAt" asc
Still stuck thought any thoughts?
For some more background info I have about 100-200 queries that I was wanting to track. For each query ran I want to pull the: user, component alterted (found w/n query's response), and the time (also found within query's response)