DQL - How to report last access time for users

[Phil_Rosier]

Is there a way to report the last access time for users?

We are embarking on an access recertification program that requires a custom entitlement report to determine whether or not access to shares or folders should be revoked. This is a complex report that will envolve both DQL and SQL due to the current limitations (and bugs) with DQL.

For each folder that has unque permissions, we currently identify users that have access to the folders and determine whether they are active or not, however the access recertification team also requires the report to show the date of when each user last accessed the folder.

Is this possible?

Thanks

Phil

[Rishi_Thaper]

Hi Phil,

This would require some post-processing in SQL.

You can utilize following DQL query to fetch last accessed time by each user for all folders under a share. Replace <SHARE_NAME> with the share in question. Note that this will not fetch the accessed time recursively for all sub-folders and that has to be computed in SQL.

DQL Query:

FROM     activity
GET     path.parent.absname,
path.parent.iscontrol_point,
path.device.name,
path.msu.name,
user.principal_name,
max(timestamp) as last_accessed
IF     
path.msu.name = "<SHARE_NAME>"
groupby
path.parent.absname,
path.parent.iscontrol_point,
path.device.name,
path.msu.name,
user.name

After that, you can post-process the results to fetch last access time in the whole hierarchy under a control point. Something like following.

SQL query:

select a2.path_parent_absname as control_point_name, a2.path_msu_name as share_name, a2.path_device_name as filer_name, a2.user_principal_name, strftime('%d-%m-%Y %H:%M:%S', max(a1.last_accessed), 'unixepoch') as last_accessed
from activity as a1
join activity as a2 on a1.path_parent_absname like a2.path_parent_absname||'%' and a2.path_parent_iscontrol_point=1 and a1.user_principal_name = a2.user_principal_name
group by a2.user_principal_name, a2.path_parent_absname
order by a2.path_parent_absname

This will group the result by control point folder and provide last accessed time by each user. Let me know if this works.

Thanks,

Rishi