Valentijn Scholten Posted August 14, 2018 Share Posted August 14, 2018 Hi, Sometimes it happens that people are leaving our company. When their account is deleted (or disabled) it can result in passwordlists having no admin anymore. As far as I can tell there's no easy way to generate a report of these lists, so that would be a welcome addition to passwordstate. Any work arounds maybe? Valentijn Link to comment Share on other sites More sharing options...
support Posted August 14, 2018 Share Posted August 14, 2018 Hello, Thanks for your request. As a work around, you could run the SQL Query below. Any Password Lists with a TotalPermissions of 0, means there is no Admin on the list. USE Passwordstate SELECT PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.Description, PasswordLists.TreePath, (SELECT COUNT(PasswordListID) FROM [PasswordListsACL] PSSWD WHERE (PSSWD.PasswordListID = PasswordLists.PasswordListID) AND (PSSWD.Permissions = 'A')) As TotalPermissions FROM [PasswordLists] WHERE (PasswordLists.PrivatePasswordList = 0) AND (PasswordLists.Folder <> 1) GROUP BY PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.Description, PasswordLists.TreePath ORDER BY PasswordLists.PasswordList Regards Click Studios Buckit 1 Link to comment Share on other sites More sharing options...
Azkabahn Posted August 27, 2018 Share Posted August 27, 2018 Hi, used this SQL query for several times. I would put +1 for this feature to have it in the reporting. I would improve with the additional column indicating the number of records in the password list. Link to comment Share on other sites More sharing options...
Valentijn Scholten Posted November 24, 2018 Author Share Posted November 24, 2018 I agree the query works fine, but it requires SQL access. I wouldn't want to allow too many servicedesk people access to the database (and bypassing audit logs). So would be nice to have the report in place. The primary usecase I would use this report is when/before deleting a user. So ideally the report would be "Password lists for which a user is the only admin". Buckit 1 Link to comment Share on other sites More sharing options...
Valentijn Scholten Posted November 24, 2018 Author Share Posted November 24, 2018 I also think the query above does not take the number of members of a group into account? I might take a look at adjusting the query as we are possibly looking at making sure all lists have at least 2 admins. Link to comment Share on other sites More sharing options...
Ulf Posted December 3, 2018 Share Posted December 3, 2018 +1 Link to comment Share on other sites More sharing options...
Jeffrey Posted December 11, 2018 Share Posted December 11, 2018 +1 Link to comment Share on other sites More sharing options...
Raytion Posted August 18, 2020 Share Posted August 18, 2020 +1 Link to comment Share on other sites More sharing options...
Mordecai Posted August 3, 2023 Share Posted August 3, 2023 +1 Link to comment Share on other sites More sharing options...
Eileen Posted August 16, 2023 Share Posted August 16, 2023 +1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now