Reports being used

I was asked recently which reports were being run by users so that some of the menus could be simplified.  This SQL will list users and the AX report name for every report that has been run by anyone.  It is a good start, but it cannot get us the last date or time that they ran the report.

SELECT UserInfo.[Name], ElementName AS [Report Name], UserInfo.[Enable] AS [Active Employee]
INTO #temp
FROM SysLastValue WITH(NOLOCK)
INNER JOIN UserInfo WITH(NOLOCK)
                ON UserInfo.ID = SysLastValue.UserID
WHERE UserID NOT IN ('Admin', '')
                AND RecordType = 18 /* Report */

SELECT t.[Report Name],
                (SELECT COUNT(DISTINCT [Name])
                                FROM #temp sub
                                WHERE sub.[Active Employee] = 1
                                                AND sub.[End User] = 1
                                                AND sub.[Report Name] = t.[Report Name]) AS [# of Active Employees Who Ran The Report]
FROM #temp t
GROUP BY [Report Name]
ORDER BY [Report Name]

DROP TABLE #temp
}

This AX job returns the label and location of all reports in the menu structure.  We can then add this information with the last used information to come up with a fairly good estimation of which reports are being used.

// Lists where all reports are located in the menu structure
// Does not include reports which are called by classes, or auto printed by a scheduled job
static void listMenuStructure(Args _args)
{
    #AOT
    #Properties
    TreeNode                treeNode;
 
    void traverse(TreeNode tn)
    {
        while (tn)
        {
            switch (tn.AOTgetNodeType())
            {
                case 205: // Folder
                    traverse(tn.AOTfirstChild());
                    break;
                case 331: // Menu reference
                    if (tn.AOTgetProperty(#PropertyName))
                    {
                        treeNode = TreeNode::findNode(#MenusPath + #AOTDelimiter + any2str(tn.AOTgetProperty(#PropertyName)) + #AOTDelimiter + 'Reports');
                        if (treeNode)
                            traverse(treeNode.AOTfirstChild());
                    }
                    break;
                case 307: // Menu item
                    setPrefix(tn.treeNodePath());
                    info(tn.AOTgetProperty(#PropertyName));
                    info(tn.AOTgetProperty(#PropertyMenuitemname));
                    break;
            }
            tn = tn.AOTnextSibling();
        }
    }
    ;
    setPrefix(funcName());
    treeNode = TreeNode::findNode(#MenusPath + #AOTDelimiter + 'MainMenu');
    treeNode = treeNode.AOTfirstChild();
    traverse(treeNode);
}