How can I Export Login History to CSV from AAI?

Products

• Automic Automation Intelligence

Issue/Introduction

How can I Export Login History to CSV from AAI?

Environment

Release : 6.3.0-1

Component : AUTOMIC AUTOMATION INTELLIGENCE ENGINE

Resolution

The query below works in SQL Server to get the user name, time of login and the logout time(in UTC).

 

You just have to update the timestamp, in milliseconds, highlighted below.  

The timestamp used below, 1628568000000, converts to 8/10/2020.

 

select u.userName,

CONVERT(DATETIME,l.timeIn/86400000.0)+'1970'as LoginTime,

CONVERT(DATETIME,l.timeOut/86400000.0)+'1970' as LogOutTime

from UserProfile u, LoginHistory l where u.userId=l.userId and l.timeIn > 1628568000000

 

You can use this link to get the timestamps in milliseconds: https://www.epochconverter.com/

 

 

 

If you right click on the results, you can select "Copy With Headers" and past it into Excel.

 

You can also setup SQL server to always include the column headers when you select "Save Results as" by following the steps below:

 

1. Go to Tools > Options

2. Navigate to Query Results > SQL Server > Results to Grid, then check “Include column headers when copying or saving the results” option:

   

3. Click OK to save changes, close and restart SSMS to apply changes