Drop the SSRS users who are not consuming SSRS reports from past 180 days

Question

I am trying to create a SSIS job to delete the SSRS users who are not consuming reports from the past 180 days. I want to this for all the SSRS reports that is present on server.

I have used

SELECT DISTINCT u.UserName 
    FROM dbo.Users u 
    LEFT JOIN dbo.ExecutionLog l 
         ON u.UserName = l.UserName 
    WHERE l.TimeStart <= DATEADD(DAY, -180, GETDATE()) OR l.TimeStart IS NULL 

Anything wrong with this query or there is any better way of doing

Answer 1

I think your query is wrong. I can't test right now and it's late here but from what I can see that would show you users who have an execution log time older than 180 days but not those that also have a log date more recently.

I would probably go about it something like this (just so I can see what I'm doing clearly - it's not the most efficient code ever :) )

SELECT u.* , d.LastLog
FROM dbo.Users u 
   JOIN ( -- Get the last time each user ran a report and filter to those that were 180 days ago or more
              SELECT UserName, Max(TimeStart) AS LastLog
                  FROM dbo.ExecutionLog
                  GROUP BY UserName 
                  HAVING MAX(TimeStart) < DATEADD(day, -180, getdate())
             ) d ON u.UserName = d.UserName

As in the comments, the subquery just gets the max execution log start time for each user and filters this list to just those older than 180 days. Then we're just joining the users table to this.