July 22, 2020 at 11:27 am
I've inventoried all our SQL Servers, Service Accts, etc in a home-grown Inventory DB.
Simplified here, my Tables\Cols\Data include:
TABLE=ServerName w/ Cols: ServerName, Active
TABLE=ServiceAccts w/ Cols: ServerName, SvcAcct, Service
ServerName Active SvcAcct Service
========== ====== ======= =======
Server123 N SvcAcct123 SQL Service
ServerABC N SvcAcctABC <--- SQL Service
ServerABC N SvcAcctABC <--- SQL Agent
ServerABC Y SvcAcctABC <--- SQL SSRS
ServerXYX N SvcAcctABC <--- SQL Service
ServerMNO Y SvcAcctMNO SQL Service
ServerMNO Y SvcAcctMNO SQL Agent
ServerLLL N SvcAcct123 SQL Service
TASK:
- Looking for T-SQL to list ONLY those ServerName's & SvcAcct's WHERE the SvcAcct is used on different ServerNames.
- Doesn't matter if the SvcAcct is Active Y or N.
In the example above:
SvcAcct 'SvcAcctABC' is used on both ServerABC and ServerXYZ
SvcAcct 'SvcAcct123' is used on both Server123 and ServerLLL
so I'd like to see output:
ServerName Active SvcAcct Service
========== ====== ======= =======
ServerABC N SvcAcctABC <--- SQL Service
ServerABC N SvcAcctABC <--- SQL Agent
ServerABC Y SvcAcctABC <--- SQL SSRS
ServerXYX N SvcAcctABC <--- SQL Service
Server123 N SvcAcct123 SQL Service
ServerLLL N SvcAcct123 SQL Service
thx for any replies here.
July 22, 2020 at 2:37 pm
That many points and no consumable data?
SvcAcct is used on different ServerNames
SELECT ServiceAccount
FROM Accounts a INNER JOIN Servers s ON a.ServerID = s.ServerID
GROUP BY ServiceAccount
HAVING COUNT(*)>1
July 22, 2020 at 2:42 pm
First, having the data in a consumable format is something that will get you a LOT more replies. The way you have it now, we have to do the leg-work to create those tables and data on our systems to test things out. This is not very "support friendly".
But to me that doesn't look too hard to do. My approach would be to add a calculated column (either in the table or in a CTE) that counts the unique SvcAcct names. So something like:
WITH [cte]
AS
(
SELECT
[ServerName]
, [SvcAcct]
, [Service]
, COUNT([SvcAcct]) OVER (PARTITION BY
[SvcAcct]
) AS [UseCount]
FROM[ServiceAccts]
)
SELECT
[sn].[ServerName]
, [sa].[Active]
, [sa].[SvcAcct]
, [sa].[Service]
FROM[ServerName] AS [sn]
JOIN[cte]
ON [cte].[ServerName] = [sn].[ServerName]
WHERE[cte].[UseCount] > 1;
NOTE - I have NOT tested the above code as I have no data to test it with. What you posted is not consumable data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2020 at 3:21 pm
Consummable data means posting the CREATE TABLE statements, with some INSERTs for sample data. Not just describing the columns.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply