Hey Everyone! Does anyone know why doing a SQL Query on the main database, the query returns 80 accounts and the GUI only shows 43 accounts?
SELECT *
FROM Accounts WITH(nolock)
Some of them may be "deleted", in that they are marked as deleted, but retained because there are activity logs related to them. When you look at the result set, is there a deleted column? If so, retry your query with the deleted flag column = 1 and you should get the correct number. Let me verify that.
Ok, so I don't have an official answer, but I suspect it's either related to linking accounts to other history or AD integration where the SID must be saved to keep the account unique. First, check is this query returns the same accounts you see in
https://{servername} /Orion/Admin/Accounts/Accounts.aspx
SELECT [AccountID]
,[Expires]
,[AccountEnabled]
,[MenuName]
,[AccountType]
FROM [SolarWindsOrion].[dbo].[Accounts]
WHERE AccountEnabled = 'Y'
I set up a new install and tried adding a new Orion (non AD) admin account "ToDelete". However, deleting it removed it from the table. I'll FWD to someone less rusty with the Accounts table. Anyone else feel free to chime in. Thanks!
The website may be listing your AD groups and not listing the individual accounts that have logged in separately. If you do a comparison, I would bet that you will find that the "extras" listed in the database are tied to the group accounts. There is a field (I think called GroupID) in the database which would show the AD group it is tied to.
So, I'm starting to think this is compounded issues from our original install over 14 years ago. We aren't fully LDAP integrated. It looks like individual accounts were created to be LDAP, but not the entire system. there are still about 10 local accounts. I deleted a couple users from the GUI and they are not appearing in the database query anymore. But these other users are still there. I think if I can create a SQL query to delete these records, we'd be fine. Does anyone know the proper way to do this?
I'd check with support first, or at least be prepared to recover from a backup. The Accounts table is tied to other tables and you'd want to scan other tables where AccountId is a foreign key first. Removing them via SWIS (SWQL) may be a better option as that should enforce relational integrity because it's using the object layer, not the tables directly.
Support really isn't responding at this moment. Since the massive security issues, they haven't responded. Giving them some time before I try and open another ticket. SWQL could be an option, but I've never used that, so I guess I'd have to do some more digging.
Found the link to deleting accounts (and adding and modifying them) with SWIS using Powershell:
https://github.com/solarwinds/OrionSDK/wiki/Account-Management
Let me know if how that works.
Sorry, I replied at the top of the thread. Here's how to get started, and I can walk you though getting the SWIS Powershell Cmdlets set up. On the plus side, it's pretty easy to get started and will let you automate Orion just about any way you like.
Found the link to deleting accounts (and adding and modifying them) with SWIS using Powershell:
https://github.com/solarwinds/OrionSDK/wiki/Account-Management
Let me know if how that works.
Thanks Patrick! I was able to go through that process and remove those random accounts. I also found this as another possible way.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.