Is there any table for "Account History" in Analytics

[Shanthi_Katkam]

Hi Team,

I was checking if there is a table with "Accounts_History", "Accountshistory", but I couldn't find with these names. Could someone please help me if  there any table for "Account History" in Analytics?

Thanks,

Shanthi Katkam

[Raghu]

@Shanthi_Katkam  Account history we dont have any table, we not saving any account history account level,

may i know what is use case and requirment please?

[PremMahadikar · Accepted solution]

Hi @Shanthi_Katkam ,

If you are looking exactly like User history table, there is no table for Account history.

But we have Audit Trail where you can set Object Type as 'ACCOUNTS' and see all the history. (Type Accounts to find it)

Note: This will only reflect the changes happened through Saviynt

Table and 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.Detail is not NULL
	and ua.TYPEOFACCESS='ACCOUNTS'

If this helps answers your question, please consider selecting Accept As Solution and hit Kudos

[Shanthi_Katkam]

Can you please provide the Column name of "Object Name" in userlogin_access table?

I have tried to fetch the column with this query "select * from userlogin_access where TYPEOFACCESS='ACCOUNTS'" but, it is not giving me the column name for 'Object Name'

[rushikeshvartak]

Account history is not gets stored in audit logs

Refer https://docs.saviyntcloud.com/bundle/Splunk-Guide/page/Content/Managing-Application-Audit-Logs.htm

[rushikeshvartak]

Object name will be property you have updated, you can search without object name you find different object ( custom property/ account type)

[Ankyt19]

you can store or check in user history if there is any change in accounts table .

Create JAR file and add all attributes , so if any changes in account table will be updated in user table and can be seen in update history 

[Shanthi_Katkam]

Hi,
User history would be updated once the rule gets triggered for same but I just wanted "Audit Trial" details in Saviny table. Is "userlogin_access" table referring to "Audit Trial" data? 

If yes, then why we are unable to see the "Object Name"(this is visible on "Audit Trail" UI) attribute in the "userlogin_access" table.  Please suggest.

Thanks,

Shanthi Katkam

[PremMahadikar]

@Shanthi_Katkam ,

Object Name attribute is present in "userlogin_access" table. The column is Objectkey.

As we are filtering based on Accounts in Objecttype, Objectkey is Accountkey here.

Try the updated code below:

select 
	ua.TYPEOFACCESS as 'Object Type',
        (select a.name from accounts a where a.accountkey=ua.OBJECTKEY) 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' 
from 
	users u , 
	userlogin_access ua, 
	userlogins l 
where 
	l.loginkey = ua.LOGINKEY 
	and l.USERKEY = u.userkey 
	and ua.Detail is not NULL
	and ua.TYPEOFACCESS='ACCOUNTS'
        and ua.OBJECTKEY is not null

If this answers your question, please consider selecting Accept As Solution and hit Kudos

[Shanthi_Katkam]

Hi @PremMahadikar 

Thanks for sharing the details. 

Use Case : We are trying to pull the history of all the actions for "Orphan Accounts" in the Analytics Report using the "Audit Trial" table but we are looking if an "Orphan Account" is mapped manually from the UI, also needs to capture that history. Is there a way please suggest.

NOTE: If we are manually actioning on the "Orphan Accounts" to correlate with user that account will no longer be an "Orphan". But we want that action also in our report.

Thanks,

Shanthi Katkam

[PremMahadikar]

@Shanthi_Katkam ,

1. To get orphan accounts in Audit trail:

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' 
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
where 
	ua.Detail is not NULL
	and ua.TYPEOFACCESS='ACCOUNTS'
    and ua.OBJECTKEY is not null
	and a.accountkey not in (select ua.accountkey from user_Accounts ua)

2. For manual actioning on the "Orphan Accounts" to correlate with user:

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' 
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
where 
    ua.Detail is not NULL
	and ua.TYPEOFACCESS='ACCOUNTS'
    and ua.OBJECTKEY is not null
    and ua.detail like '%updated username%'

Please do union of both the codes to fetch the results.

Note: The above query is written with few logics using test data in my system. Please use it has reference and add more filters depending on your use case.

If this helps your question, please consider selecting Accept As Solution and hit Kudos