Report Generation- How to generate a report for inactive users in a particular period.

Hello,

ADSAccounts or Person (Employee) objects?  An actual report using the report editor or just a SQL query?

You can use the disabled flags in each case (AccountDisabled and IsInActive), but you need to look at how to determine the time period.  You can use the "updated" fields in each case, but it might not be completely accurate, i.e the account is disabled one day, but an update occurs after that, for whatever reason.

So you could look at using the column level logging in each case to determine when the actual column value was changed, etc.

It just really depends what you're looking for specifically, as per my first question.

Trevor

If you have TimeTrace activated you can create a report using a historical data query as described here Data Retrieval using Multiple Object History .

Hello Trevor,

Thanks for your reply, I am looking for a SQL query to create a report from Person table. 

• Lastname
• Firstname
• Username
• Disabled Date

The report should contains users were inactivated between 01.01.2017 and 31.05.2017.I have tried with the 'XDateUpdated' but I could see that the result was not completely accurate.

Please share you have an SQL query for the same.

As written in my other reply, if you have TimeTrace activated you are able to build a report that is able to fetch the information using historical data queries, even if the historical data has been moved to one of the connected historical databases.

If your historical data for the range in question is still in the main database and the persons have not been deleted yet you can retrieve the information with the following query.

select P.LastName, P.FirstName, P.CentralAccount, q.OperationDate as DisabledDate
from 
QBMWatchOperationSummary q
join Person p on q.ObjectKeyOfRow = p.XObjectKey and q.TableName='Person'
where 
q.ColumnName = 'IsInactive'
and q.ContentShort = '1'
and q.OperationDate >= '2017-01-01' 
and q.OperationDate < '2017-06-01'