ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Connect Discussions

Find answers, ask questions, and share expertise about Alteryx Connect.
SOLVED

Audit UsageReport in Alteryx Connect

srivallak
7 - Meteor

I am trying to pull some usage activity of Connect users from H2 database and unable find the related tables where I can pull the below information.

 

1) Number of users who logged in to connect as Contributor, Certified and Readers Access ?

2) Count of Active users who accessed Alteryx workflows, reports and data sources..etc?

3) How many Assets got certified?

 

Can some one provide any insights on this? Any documentation is appreciated

 

Thanks

Srinath

OndrejC
Alteryx
Alteryx

Hi @srivallak ,

1) Number of users who logged in to Connect as Contributor, Certifier and Reader?
You would need to query following tables:

  • EVENT_AUDIT - to query login event for every user

 

select * from STD_XFORMS.EVENT_AUDIT ORDER BY EVENT_DATE DESC;

 

  • ENTRY - to query for specific group (query for the current version of entry 'Certifiers')

 

select * from STD_XFORMS.ENTRY where ENTRY_NAME='Certifiers' and ENTRY_CURRENT='TRUE';

 

  • ATTRIBUTE - to get membership attribute of an entry Certifiers query for the 'member' key which contain the list of members prefixed with "xf3:"

 

select *  from STD_XFORMS.ATTRIBUTE where ATTR_KEY='member' AND ENTRY_XID='f42ca4e9-e201-4ac8-9f16-1410aeb48f1d' order by entry_version DESC limit 1;

 

OndrejC_0-1595841863901.png


Ondrej

OndrejC
Alteryx
Alteryx

2) Count of Active users who accessed Alteryx workflows, reports and data sources..etc?

 

You need to query following tables:

  • EVENT_MONITOR - to query for specific event type e.g. VIEW or LAUNCH (open button)

 

select * from STD_XFORMS.EVENT_MONITOR order by time desc;

 

  • ENTRY - to get asset details (e.g name of the asset = workflow name)

 

select * from STD_XFORMS.ENTRY where ENTRY_CURRENT='TRUE' and ENTRY_XID='NWQ5Y2UwMGQwZDA2YjkyOTZjNzg5ZDE5';

 

OndrejC_0-1595842825268.png

 

OndrejC
Alteryx
Alteryx

3) How many Assets got certified?

Query EVENT_MONITOR table for EVENT_TYPE 'Certify'/'Decertify'/'Do Not Use'

 

SELECT * FROM STD_XFORMS.EVENT_MONITOR
 WHERE EVENT_TYPE='Certify';
OndrejC
Alteryx
Alteryx

+ one more thing is that with you queries to H2 you need to be careful not to put too much load on h2 since it may affect your user experience.

 

Better way how to extract information from Connect and process them on your side would be Connect REST API,see documentation here: https://help.alteryx.com/20202/connect/develop

 

Hope this helps,
Ondrej

EricWe
Alteryx
Alteryx

Hi @srivallak

 

I reviewed H2 access options with @OndrejC. He mentioned that using the REST API is the most robust solution. However, AUDIT_MONITOR about logins and events is not exposed.

 

There are two other options that require less parsing of responses from API. See these articles for details.

Connect to the H2 database 

H2 Console 

 

Please contact support@alteryx.com for any assistance needed.

srivallak
7 - Meteor

Thank you for Ondrej. I really appreciate your solution. Let me try connecting through API

srivallak
7 - Meteor

Hi Eric

 

Can you provide me the steps how to connect ConnectAPI through Alteyx designer in case if i need grab information from Entry Table

 

Thanks

Sri