Snowflake: query_history_by_user() does not yield expected result

November 26, 2021

Snowflake provides the QUERY_HISTORY_BY_USER() function to actually see the queries from any user. I’m using this command for querying:

select * from table (
  <DATABASE>.<SCHEMA>.query_history_by_user(
    USER_NAME=>'myUser',
    END_TIME_RANGE_START=>to_timestamp_ltz('2021-11-24 06:00:00 -0100'),
    END_TIME_RANGE_END=>to_timestamp_ltz('2021-11-24 17:00:00 -0100'),
    RESULT_LIMIT=>100)
);

The problem is that this query does not yield any results. However, if I start the same query without the USER_NAME=>'myUser' statement – which is semantically the same, since the executing user is taken as a default – I do get the expected result. The result for a different user than mine (i.e. USER_NAME=>'differentUser') is also empty. All queries are executed as ACCOUNTADMIN, so rights are presumably not the problem.

So the question is: why does the query work without any user provided but does not work with a user specified?

Any help is really appreciated!

>Solution :

I sugest wrapping user name with ": USER_NAME=>'differentUser' => USER_NAME=>'"differentUser"'

QUERY_HISTORY_BY_USER

A string specifying a user login name or CURRENT_USER. Only queries run by the specified user are returned. Note that the login name must be enclosed in single quotes.

Also, if the login name contains any spaces, mixed-case characters, or special characters, the name must be double-quoted within the single quotes (e.g. ‘"User 1"’ vs ‘user1’).