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.
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply