Looking for custom T-SQL

  • Express12

    SSCoach

    Points: 18460

    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

    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

    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

    Consummable data means posting the CREATE TABLE statements, with some INSERTs for sample data. Not just describing the columns.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply