How can I Export Login History to CSV from AAI?
Article ID: 222221
Updated On:
Products
Issue/Introduction
How can I Export Login History to CSV from AAI?
Environment
Release : 24.4.2 and earlier
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:
SELECT
u.userName,
ISNULL(d.domainName, '(JAWS internal account)') AS domain_name,
CASE
WHEN d.domainType = 'eiam' THEN 'EEM / SSO'
WHEN d.domainType = 'ldap' THEN 'LDAP'
WHEN d.domainType = 'kerberos' THEN 'Kerberos'
ELSE 'JAWS internal'
END AS auth_type,
lh.host,
lh.clientTypeId,
DATEADD(
millisecond, lh.timeIn % 86400000,
DATEADD(day, lh.timeIn / 86400000, CAST('19700101' AS DATETIME2(3)))
) AS login_time_utc,
CASE WHEN lh.timeOut IS NULL THEN NULL
ELSE DATEADD(
millisecond, lh.timeOut % 86400000,
DATEADD(day, lh.timeOut / 86400000, CAST('19700101' AS DATETIME2(3)))
)
END AS logout_time_utc
FROM LoginHistory lh
JOIN UserProfile u ON u.userId = lh.userId
LEFT JOIN LdapDomain d ON d.ldapDomainId = u.ldapDomainId
WHERE lh.timeIn >= DATEDIFF_BIG(millisecond, '19700101', DATEADD(day, -30, GETDATE()))
AND u.userName <> 'admin'
ORDER BY lh.timeIn DESC;
For Oracle use the query below:
SELECT
u.username,
NVL(d.domainname, '(JAWS internal account)') AS domain_name,
CASE
WHEN d.domaintype = 'eiam' THEN 'EEM / SSO'
WHEN d.domaintype = 'ldap' THEN 'LDAP'
WHEN d.domaintype = 'kerberos' THEN 'Kerberos'
ELSE 'JAWS internal'
END AS auth_type,
lh.host,
lh.clienttypeid,
TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL(lh.timein / 1000, 'SECOND') AS login_time_utc,
CASE WHEN lh.timeout IS NULL THEN NULL
ELSE TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL(lh.timeout / 1000, 'SECOND')
END AS logout_time_utc
FROM AAI.loginhistory lh
JOIN AAI.userprofile u ON u.userid = lh.userid
LEFT JOIN AAI.ldapdomain d ON d.ldapdomainid = u.ldapdomainid
WHERE lh.timein >= (SYSDATE - 30 - DATE '1970-01-01') * 86400000 -- last 30 days
AND u.USERNAME !='admin'
ORDER BY lh.timein DESC;
Additional Information
Note that these queries exclude the Admin user which in some environments will display many logins as it is used by AAI micro services to communicate with the AAI API.
Feedback
