Looking for custom T-SQL

• Express12

  SSCoach

  Points: 18460

  More actions

  July 22, 2020 at 11:27 am

  #3773698

  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.

  BT

• pietlinden

  SSC Guru

  Points: 62890

  More actions

  July 22, 2020 at 2:37 pm

  #3773818

  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

• Mr. Brian Gale

  SSC-Insane

  Points: 23150

  More actions

  July 22, 2020 at 2:42 pm

  #3773819

  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.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 720887

  More actions

  July 22, 2020 at 3:21 pm

  #3773830

  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