Events table: Difference between execute_query/data_source and execute/query?

[rsmaldone]

Issue Summary

We’re looking to get some insight into user activity in Redash. When a user executes a query, it appears that query execution activity is being logged in two different ways. Sometimes it’s recorded as object_type = 'query' and action = 'execute', like I would expect. But more often, it’s showing up as object_type = 'data_source' and action = 'execute_query'

I’ve confirmed that when I go into a dashboard and click refresh on a widget, or view a query and click “Execute”, it’s being recorded in the events table as object_type = 'data_source' and action = 'execute_query'. This is less helpful, since it logs the id of the data source in this case, and not the id of the query. Meanwhile, the text of the executed query appears in the additional_properties field.

Can you help me understand this behavior, and what a good solution would be to identify the queries being used in these situations?

Thank much!

Technical details:

• Redash Version: 9.0.0-beta (2641562b)
• Browser/OS: 89.0.4389.114 (Official Build) (64-bit) / Windows 10 19042.867

[k4s1m]

@rsmaldone

But more often, it’s showing up as object_type = 'data_source' and action = 'execute_query'

This is the expected behavior.

@rsmaldone

Sometimes it’s recorded as object_type = 'query' and action = 'execute' , like I would expect.

This doesn’t make sense and shouldn’t be possible. From searching the source code, the only actions for query are

• favorite
• search
• list
• create
• view
• regnerate_api_key [typo?]
• fork
• load_favorites

@rsmaldone

This is less helpful, since it logs the id of the data source in this case, and not the id of the query .

Are you sure? The query ID is logged within additional properties also. Try running this query against your metadata DB and you’ll see the query id also:

SELECT additional_properties::JSON ->> 'query_id'
FROM EVENTS
WHERE action = 'execute_query'
AND additional_properties::JSON ->> 'query_id' <> 'adhoc'

[rsmaldone]

That’s incredibly helpful! Thank you so much, that’s exactly what I was looking for.

Also for reference, when I run a query on the events table to see all possible actions for object_type = 'query', here’s what we get back:

SELECT action, count(*)
FROM events
WHERE object_type = 'query'
GROUP BY 1
ORDER BY 1

action	count
api_get	1004
cancel_execute	742
create	893
edit_description	38
edit_name	2583
edit_schedule	54
edit_tags	73
execute	71190
favorite	487
fork	1165
list	11048
load_favorites	32504
search	11766
toggle_published	405
update_data_source	3598
view	107877
view_source	15723

So perhaps it varies by version.

[k4s1m]

I figured it out where they’re coming from.

On the backend, the event is object_type=data_source and the action=execute_query.

But on the frontend, when a user executes a query, this creates the object_type=query and action=execute event.

[kaladin]

Hey, thanks for creating this thread - I’m working on something similar and this is quite helpful

@k4s1m : I just had one doubt - didn’t quite get what you mean here? :

@k4s1m

On the backend, the event is object_type=data_source and the action=execute_query.

But on the frontend, when a user executes a query, this creates the object_type=query and action=execute event.

I am looking to get a list of all queries that were run at any time on Redash. Does that mean I will have to consider both

action = 'execute_query' and action = 'execute',

OR, does just action = 'execute_query' cover everything?