Hi Experts,
Hope you are doing well.
Use Case: Generate the report for orphan accounts where someone has added user or changed any attributes from accounts page.
Issue : I have created the query but that query also picking the normal accounts performed action as well. but we have to exclude that.
Run time Query:
select
ua.TYPEOFACCESS as 'Object Type',
a.name as 'Object Name',
ua.ActionType as 'Action Taken',
u.username as 'Accessed By',
ua.IPADDRESS as 'IP Address',
ua.ACCESSTIME as 'Event Time',
ua.DETAIL as 'Message',
e.endpointname
from
users u
left join userlogins l on l.USERKEY = u.userkey
left join userlogin_access ua on l.loginkey = ua.LOGINKEY
left join accounts a on a.accountkey = ua.OBJECTKEY
left join endpoints e on e.endpointkey = a.endpointkey
where
ua.Detail is not NULL
and ua.TYPEOFACCESS = 'ACCOUNTS'
and ua.OBJECTKEY is not null
and ua.ActionType in ('Update')
and a.accountkey not in (
select
ua.accountkey
from
user_Accounts ua
)
and ua.accesstime >= Subdate(
Sysdate(),
INTERVAL '${days}' day
)
UNION
select
ua.TYPEOFACCESS as 'Object Type',
a.name as 'Object Name',
ua.ActionType as 'Action Taken',
u.username as 'Accessed By',
ua.IPADDRESS as 'IP Address',
ua.ACCESSTIME as 'Event Time',
ua.DETAIL as 'Message',
e.endpointname
from
users u
left join userlogins l on l.USERKEY = u.userkey
left join userlogin_access ua on l.loginkey = ua.LOGINKEY
left join accounts a on a.accountkey = ua.OBJECTKEY
left join endpoints e on e.endpointkey = a.endpointkey
where
ua.Detail is not NULL
and ua.TYPEOFACCESS = 'ACCOUNTS'
and ua.OBJECTKEY is not null
and a.name in (
select
a.name
from
users u
left join userlogins l on l.USERKEY = u.userkey
left join userlogin_access ua on l.loginkey = ua.LOGINKEY
left join accounts a on a.accountkey = ua.OBJECTKEY
left join endpoints e on e.endpointkey = a.endpointkey
where
ua.Detail is not NULL
and ua.TYPEOFACCESS = 'ACCOUNTS'
and ua.OBJECTKEY is not null
and ua.ActionType in ('Update')
and ua.detail like '%updated username%'
)
and ua.ActionType in ('Update')
and ua.accesstime >= Subdate(
Sysdate(),
INTERVAL '${days}' day
)
Your guidance would be highly valued for us in moving forward.
Please suggest if there is any other way to achieve the same scenario.
Thanks
Sandeep
