Account Owners Report

[Community_User]

Originally posted on June 8 2020 at 16:26 UTC

Hi there,

Recently installed v5.4, and we have applied owners to various AD service accounts for a Service Account Campaign. I want to run a report that shows me who the owners are of those accounts.

If I select "Accounts" from the "Admin" tab, I can see the accounts and attributes below the column headings, but I can't see any option to add any further columns to the default 9.

I would have thought this would have been a simple task, and maybe it is and I'm missing something here, but it doesn't seem possible our of the box?

Thanks

[Community_User]

Originally posted on June 9 2020 at 03:08 UTC

Hi David,

You can create an analytic control to get the owners for the accounts.

I am unable to get " I can't see any option to add any further columns to the default 9. " default 9 means. Please elaborate so that i can help you on same.

Thanks

Ajay

[Community_User]

Originally posted on June 9 2020 at 08:29 UTC

Hi there,

Thanks for coming back. What I mean by the "Default 9" is that on the "Accounts" window you see the following 9 columns: Account Name, Display Name, User Name, Security System, Endpoint, Account Type, Comments, Description, Status. There is no option to add other columns to this view such as "Owner". The "owner" attribute is a default attribute for every account, and is fundamental to SSM Service Account Campaigns.

Do I really have to start learning SQL statements to create an analytical control to add something so basic?

Thanks

[Community_User]

Originally posted on June 9 2020 at 09:37 UTC

Hi David,

The owner option is not available on list page as you can have more than one owner for the account with different rank. Thus if you wish to view account owners, you can navigate to owner tabe for each account to fecth the owners available for the account.

Thanks

Ajay

[Community_User]

Originally posted on June 9 2020 at 12:54 UTC

Hi Ajay,

Accessing each account to look up the owner isn't an option.

So my only choice is to create a new analytic control and find a developer to write SQL queries for the control? Is that really where I am at?

I just want a report that shows the owners of the AD accounts that have owners.

Thanks

[Community_User]

Originally posted on June 10 2020 at 02:24 UTC

Hi David,

Below is the query which you can use to get the list of accounts with owners, with the type of owner i.e. either user or usergroup, corresponding name and rank with account details. I hope this would take care of your requirement.

select case when Username is null then 'Usergroup' else 'User' end as OwnerType,Username, User_Groupname, Rank,Accounttype, Application, Account_Name, Account_Status, case when User_Status is null then 'N/A' when User_Status = 1 then 'Active' else 'InActive' end as User_Status from(

select u.Username, ug.User_Groupname, a.Accounttype,e.endpointname as 'Application', a.name as 'Account_Name', a.status as Account_Status, u.statuskey as User_Status, ao.rank as Rank

from accountowners ao join accounts a on a.ACCOUNTKEY = ao.ACCOUNTKEY join endpoints e on a.ENDPOINTKEY = e.ENDPOINTKEY join securitysystems ss on e.SECURITYSYSTEMKEY = ss.SYSTEMKEY left join users u on u.USERKEY = ao.owneruserkey left join user_groups ug on ug.usergroupkey=ao.OWNERUSERGROUPKEY where a.status in ("Manually Provisioned",1,'Active') and ss.STATUS=1 and e.status=1)t;

Thanks

Ajay

[Community_User · Accepted solution]

Originally posted on June 10 2020 at 14:13 UTC

Hey Ajay,

That's brilliant. Thank you.