Hi Roberto,
I recently used the following query to highlight tasks most used:
select TASK.FRIENDLYNAME, TASK.TASKTAG, vst.num_runs
from IM_OS.dbo.IM_TASK as TASK
LEFT OUTER JOIN
(select name, COUNT(name) as num_runs
from IM_TP.dbo.tasksession12_5
where
user_dn != '<userid_inbound>' -- exclude inbound
and environmentid=<envid_to_analyze> -- used environment
GROUP BY name
UNION ALL
select name, COUNT(name) as num_runs
from IM_AR.dbo.archive_tasksession12_5
where
user_dn != '<userid_inbound>' -- exclude inbound
and environmentid=<envid_to_analyze> -- used environment
GROUP BY name) as vst
ON TASK.FRIENDLYNAME = vst.name
where
TASK.ENV_OID = <envid_to_analyze> -- used environment
order by vst.num_runs DESC
where
<userid_inbound> = userid inbound user
<envid_to_analyze> = id of the IM environment to be analyzed
IM_OS = Object store DB
IM_TP = Task Persistence DB
IM_AR = Archive DB
Starting from the IM_TASK table, it will show also not used task.
You would also like to add some time restriction to show only task used in the last months, year…
last_access_time > DATEADD(mm,-3,GETDATE()) /*3 month */
I hope this can help you.
Ciao
Fabrizio