topic Re: Audit UsageReport in Alteryx Connect in Alteryx Connect Discussions

Audit UsageReport in Alteryx Connect

srivallak — Tue, 21 Jul 2020 14:44:50 GMT

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

Re: Audit UsageReport in Alteryx Connect

OndrejC — Mon, 27 Jul 2020 09:25:03 GMT

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;

Ondrej

Re: Audit UsageReport in Alteryx Connect

OndrejC — Mon, 27 Jul 2020 09:41:30 GMT

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';

Re: Audit UsageReport in Alteryx Connect

OndrejC — Mon, 27 Jul 2020 09:56:55 GMT

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';

Re: Audit UsageReport in Alteryx Connect

OndrejC — Mon, 27 Jul 2020 10:02:34 GMT

+ 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

Re: Audit UsageReport in Alteryx Connect

EricWe — Mon, 27 Jul 2020 13:21:29 GMT

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.

Re: Audit UsageReport in Alteryx Connect

srivallak — Mon, 27 Jul 2020 15:45:18 GMT

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

Re: Audit UsageReport in Alteryx Connect

srivallak — Wed, 05 Aug 2020 15:02:59 GMT

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