Run/create a query/datasource to Central Management Server query in SSRS

  • Simply put, you can run a query against all servers in the central management server group using SSMS. Does anyone know how to create a reporting services datasource that would mimic this action?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Bumping this thread as this is something I'm looking to implement as well.

  • (bump) ... I would also like to do this if anyone knows how to go about it. I'm considering doing my CMS work and pointing SSRS to the resultant data, but I'd rather touch the CMS directly.

  • Anyone find an answer for this one?

  • While not overly thrilled with the idea, what I ended up doing was simply creating stored procedures with open datasource calls and dynamically calling the server based on a parameter that fed from my central list of inventory.

  • Hi,

    Can i see your stored procedure? I want to do the same thing to create SSRS report for 30+ different servers.

  • i put together this prototype;

    basically, for every SQL server in msdb.[dbo].[sysmanagement_shared_registered_servers_internal], i'm creating a linked server for it, and passing commands;

    my example is just running a fancy @@version++ inventory query.

    the try-catch is a definite plus, because some servers might be offline, deprecated, or inaccessible, and could stop the query without it.

    --##################################################################################################

    --Get the list of SQL servers from Central management Server, and make sure we have a linked server by the same name.

    --##################################################################################################

    -- select * from msdb.[dbo].[sysmanagement_shared_server_groups_internal]

    -- select * from msdb.[dbo].[sysmanagement_shared_registered_servers_internal]

    DECLARE

    @isql VARCHAR(max),

    @svrname NVARCHAR(128),

    @SVR NVARCHAR(128)

    declare c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for

    --###############################################################################################

    --cursor definition

    --###############################################################################################

    WITH KnownClusteredNodes(name)

    AS

    (

    SELECT 'XXX-SQL-P200' UNION ALL

    SELECT 'XXX-SQL-P201' UNION ALL

    SELECT 'XXX-SQL-P01' UNION ALL

    SELECT 'XXX-SQL-P02' UNION ALL

    SELECT 'XXX-SQL-C01' UNION ALL

    SELECT 'XXX-SQL-C02' UNION ALL

    SELECT 'XXX-BI0-P01' UNION ALL

    SELECT 'XXX-BI0-P02'

    )

    select

    name,

    server_name

    from msdb.[dbo].[sysmanagement_shared_registered_servers_internal]

    WHERE server_type = 0 --SQL server

    AND name not in (SELECT name FROM KnownClusteredNodes)

    --###############################################################################################

    open c1

    fetch next from c1 into @svrname,@svr

    While @@fetch_status <> -1

    begin

    IF NOT EXISTS(SELECT * FROM master.sys.servers WHERE name = @svrname)

    BEGIN

    PRINT 'Adding Linked Server ' + quotename(@svrname)

    --EXEC master.dbo.sp_addlinkedserver @server = @svrname, @srvproduct=N'SQL Server'

    --EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@svrname,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    END

    fetch next from c1 into @svrname,@svr

    end

    close c1

    deallocate c1

    --##################################################################################################

    --query the servers for :

    --##################################################################################################

    --bad or invalid Central Management Servers

    DECLARE

    @MyCommand AS VARCHAR(max),

    @val AS VARCHAR(128),

    @ErrorNumber AS INT,

    @ErrorSeverity AS INT,

    @ErrorState AS INT,

    @ErrorLine AS INT,

    @ErrorProcedure AS NVARCHAR(126),

    @ErrorMessage AS NVARCHAR(4000),

    @ErrorString AS NVARCHAR(4000)

    declare linkedCursor cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for

    --###############################################################################################

    --cursor definition

    --###############################################################################################

    select name from master.sys.servers where product = 'SQL Server' AND provider ='SQLNCLI' and server_id > 0

    --###############################################################################################

    open linkedCursor

    fetch next from linkedCursor into @val

    While @@fetch_status <> -1

    BEGIN

    BEGIN TRY

    SELECT @MyCommand = 'SELECT * FROM OPENQUERY(' + quotename(@val) + ',''Select @@version As Version,

    Serverproperty(''''BuildClrVersion'''') AS BuildClrVersion,

    Serverproperty(''''ComputerNamePhysicalNetBIOS'''') AS ComputerNamePhysicalNetBIOS,

    Serverproperty(''''Edition'''') AS Edition,

    CASE Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 4)

    + ''''.''''

    + Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 3)

    WHEN ''''9.00'''' THEN ''''SQL 2005''''

    WHEN ''''10.0'''' THEN ''''SQL 2008''''

    WHEN ''''10.50'''' THEN ''''SQL 2008R2''''

    WHEN ''''11.0'''' THEN ''''SQL 2012''''

    WHEN ''''12.0'''' THEN ''''SQL 2014''''

    WHEN ''''13.0'''' THEN ''''SQL 2016''''

    ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 4)

    + ''''.''''

    + Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 3)

    END As SQLVersion,

    Serverproperty(''''productversion'''') As ProductVersion,

    Serverproperty(''''EditionID'''') AS EditionID,

    Serverproperty(''''EngineEdition'''') AS EngineEdition,

    Serverproperty(''''MachineName'''') AS MachineName,

    Serverproperty(''''ProductLevel'''') AS ProductLevel,

    Serverproperty(''''ResourceLastUpdateDateTime'''') AS ResourceLastUpdateDateTime,

    Serverproperty(''''ResourceVersion'''') AS ResourceVersion,

    Serverproperty(''''ServerName'''') AS ServerName,

    Serverproperty(''''InstanceName'''') AS InstanceName '')'

    EXECUTE (@MyCommand)

    END TRY

    BEGIN CATCH

    SELECT

    @ErrorNumber = Error_number(),

    @ErrorSeverity = Error_severity(),

    @ErrorState = Error_state(),

    @ErrorLine = Error_line(),

    @ErrorProcedure = Error_procedure(),

    @ErrorMessage = Error_message();

    SELECT

    @ErrorString = '*** Error Num: '

    + CONVERT (VARCHAR(5), CONVERT(INT, @ErrorNumber, 0))

    + ' Severity: '

    + CONVERT (VARCHAR(5), CONVERT(INT, @ErrorSeverity, 0))

    + ' State: '

    + CONVERT (VARCHAR(5), CONVERT(INT, @ErrorState, 0))

    + ' Line: '

    + CONVERT (VARCHAR(5), CONVERT(INT, @ErrorLine, 0))

    + ' Procedure: '

    + Isnull(@ErrorProcedure, '') + ' Message: '

    + Isnull(@ErrorMessage, '');

    PRINT @ErrorString

    END CATCH

    fetch next from linkedCursor into @val

    end

    close linkedCursor

    deallocate linkedCursor

    --failed jobs

    --offline databases.

    --##################################################################################################

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks. I think i can modify this stored procedure to get any information like database information,job information, backup information etc right?

  • It would behoove you to look into a PowerShell solution instead. Just my 2 cents.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • I have never done any powershell script so do not know how but i will search and see how i can do it.

  • Thanks so much Lowell.

Viewing 11 posts - 1 through 10 (of 10 total)

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