Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to get Event Access time in Audit Report

poonammhetre
New Contributor II
New Contributor II

Hi ,

We have created the Audit report using below OOTB query.

select ua.TYPEOFACCESS as 'Object Type',ua.ActionType as 'Action Taken',u.username as 'Accessed By', ua.IPADDRESS as 'IP Address',ua.ACCESSTIME as 'Event Time',ua.DETAIL as 'Message' from users u , userlogin_access ua, userlogins l where l.loginkey = ua.LOGINKEY and l.USERKEY = u.userkey and ua.AccessTime >= (NOW() - INTERVAL ${timeFrame} Minute) and ua.Detail is not NULL

 

Can anyone help me with the changes to get the accesstime in milliseconds?  Currently ACCESSTIME is returning timestamp value. eg. 2024-04-06 09:17:46

How to convert it to milliseconds in sql query?

 

Thanks,

Poonam

 

2 REPLIES 2

PremMahadikar
Valued Contributor
Valued Contributor

Hi @poonammhetre ,

Can you try below code in your query:

select (UNIX_TIMESTAMP('2024-04-06 09:17:46')*1000) AS 'date_in_milliseconds'

Its working for me

PremMahadikar_0-1713188220677.png

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

rushikeshvartak
All-Star
All-Star

rushikeshvartak_0-1713237555991.png

SELECT ua.typeofaccess AS 'Object Type',
       ua.actiontype   AS 'Action Taken',
       u.username      AS 'Accessed By',
       ua.ipaddress    AS 'IP Address',
       ua.accesstime   AS 'Event Time',
       ua.detail       AS 'Message',
       (Unix_timestamp(accesstime) * 1000) 'Event TIme in milliseconds '
FROM   users u ,
       userlogin_access ua,
       userlogins l
WHERE  l.loginkey = ua.loginkey
AND    l.userkey = u.userkey
AND    ua.accesstime >= (Now() - interval ${timeFrame} minute)
AND    ua.detail IS NOT NULL

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.