I was recently asked to take a look at why the Applications Per User report wasn't returning any data. I initially thought it was timing out (as regardless of criteria used it does take a while to run). This was not what the problem was, the following is what I carried out to fix it...

First place to try was to see what happened when I ran the underlying stored procedure directly against the database. In the example the user was attempting to find all applications containing the word 'Acrobat' so the query I tried was....

EXEC StockReportApplicationsPerUser 1, 1, 'AND ((App.[Name] LIKE ''%acrobat%''))'

When I ran this, after 5 minutes or so I go the following...

...OK a clue of a potential data issue. Quick search of similar text on Snow Globe uncovered this KB article. Not exactly the same issue but gave a good direction of where to look. Querying the table dbo.tblComputerApplicationUsers and sorting by UsedMinutes ascending showed a number of negative values (not sure how this can even happen, but anyway)....

As per the article, I set these to 0 and tried to run the report again. Still not working, same issue.

After running some additional database tracing to see what query was running in the report stored procedure, I found the following step in the SP SQL was the cause...

Working on the assumption that the UsedMinutes column was still the issue, I ran my own query to see the results of summing this data by application and user...

I noted a couple of really big numbers (which incidentally appear to be associated with the user root and for the applications 'cron (Mac) -NonApp-' and 'McAfee Agent (Mac)').

Working on the assumption that the big values (3,866,634,176 and 3,795,775,342) were attempting to be inserted into a temp table or indeed a function that only works with INT datatypes and are just too large to fit the max value that this can accommodate (2,147,483,647).

I ran an update statement across tblComputerApplicationUsers to set the UsedMinutes value for the specific user/applications to 0 and tried the report again. This time it worked....😀

I will feed these finding back to Snow in a ticket to get the issue tracked.