Analytics Table Column name

[Imran]

Hi Team,

We need to fetch the username who created the Analytics Report. Can you please help with the Cloumn name to fetch the details?

Regards,

Imran Syed

[rushikeshvartak]

SELECT a.analyticsname,
       a.displayname,
       a.description,
       ah.fieldname,
       ah.newvalue,
       ah.oldvalue,
       ah.updatedate,
       u.username AS updatedby
FROM   analyticsconfig_history ah,
       analyticsconfig a,
       users u
WHERE  ah.fieldname LIKE '%Creating New Analytics%'
       AND ah.analyticsconfigkey = a.analyticskey
       AND u.userkey = ah.updateuser
UNION
SELECT a.analyticsname,
       a.displayname,
       a.description,
       ah.fieldname,
       ah.newvalue,
       ah.oldvalue,
       ah.updatedate,
       u.username AS updatedby
FROM   analyticsconfiges_history ah,
       analyticsconfiges a,
       users u
WHERE  ah.fieldname LIKE '%Creating New Analytics%'
       AND ah.analyticsconfigeskey = a.analyticskey
       AND u.userkey = ah.updateuser 

[Imran]

Hi Rushikesh - The above query is giving zero results.

[rushikeshvartak]

Check action name from below query and update in above query. Shared query worked in v5.5

Select distinct fieldname from analyticsconfiges_history 

che

[rushikeshvartak]

Updated query 

SELECT a.analyticsname,
       a.displayname,
       a.description,
       ah.fieldname,
       ah.newvalue,
       ah.oldvalue,
       ah.updatedate,
       u.username AS updatedby
FROM   analyticsconfig_history ah,
       analyticsconfig a,
       users u
WHERE  ah.fieldname LIKE '%Creat%'
       AND ah.analyticsconfigkey = a.analyticskey
       AND u.userkey = ah.updateuser
UNION
SELECT a.analyticsname,
       a.displayname,
       a.description,
       ah.fieldname,
       ah.newvalue,
       ah.oldvalue,
       ah.updatedate,
       u.username AS updatedby
FROM   analyticsconfiges_history ah,
       analyticsconfiges a,
       users u
WHERE  ah.fieldname LIKE '%Creat%'
       AND ah.analyticsconfigeskey = a.analyticskey
       AND u.userkey = ah.updateuser 

[Rajesh-R]

@Imran - Please try below:

SELECT a.analyticsname,
       a.displayname,
       a.description,
       ah.fieldname,
       ah.newvalue,
       ah.oldvalue,
       ah.updatedate,
       u.username AS updatedby
FROM   analyticsconfiges_history ah,
       analyticsconfiges a,
       users u
WHERE  ah.fieldname LIKE '%Analytics created%'
       AND ah.analyticsconfigeskey = a.analyticskey
       AND u.userkey = ah.updateuser;

[Manu269]

@Imran can you check below query :

Note : There are few columns which cannot be used as IS. example update date as it gives error and cant be used. Hence alias has to be added.

SELECT a.analyticsname As 'Analytics Name',
a.displayname as 'Analytics Display Name',
a.description as 'Analytics Description',
ah.fieldname,
ah.updatedate as 'Date Update',
u.username AS 'Updated By'
FROM analyticsconfiges_history ah,
analyticsconfiges a,
users u
WHERE ah.fieldname LIKE '%Analytics created%'
AND ah.analyticsconfigeskey = a.analyticskey
AND u.userkey = ah.updateuser;