Query to show NSE queue status across multiple SMP's

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

Customers with multiple SMP's may like to monitor their NSE backlog from one SQL Server, or SMP, or to create a Report that can be emailed to all Admins.  The following query makes this possible.

NOTE: this does require Linked Servers.  If you have setup IT Analytics you should have Linked Servers already setup.

Resolution

Use the SQL query below to create a Report.

SQL Query is as follows: 

-- 1. Define the placeholder token used in the template
declare @servernametoken as nvarchar(max) = 'SQLServer'

-- 2. Define your actual SQL Server names (or Linked Server names)  HINT: execute "select * from sys.servers" to show linked  SQL servers
declare @server1 as nvarchar(max) = '[YourServerName1]';  -- SQLServer1
--declare @server2 as nvarchar(max) = '[YourServerName2]';  -- SQLServer2
--declare @server3 as nvarchar(max) = '[YourServerName3]';  -- etc...
--declare @server4 as nvarchar(max) = '[YourServerName4]';
--declare @server5 as nvarchar(max) = '[YourServerName5]';
--declare @server6 as nvarchar(max) = '[YourServerName6]';

-- 3. Create the query
-- Don't make any changes to section 3 --
declare @SQL as nvarchar(max) = 'select ''' + @servernametoken + ''' as [Database],
(select SUM(totalsize)/1024/1024 from ' + @servernametoken + '.[Symantec_CMDB].[dbo].eventqueue) as [Total Queue Size (MB)],
(select count(*) from ' + @servernametoken + '.[Symantec_CMDB].[dbo].EventQueueEntry) as [EventQueueEntry],
(select COUNT(*) from ' + @servernametoken + '.[Symantec_CMDB].[dbo].Evt_NS_Event_History where StartTime > DATEADD(MINUTE, -1, GETDATE())) as [NSE Processed /min],
(select Max(currentrequests) from ' + @servernametoken + '.[Symantec_CMDB].[dbo].Evt_NS_Client_Config_Request where StartTime > DATEADD(MINUTE, -10, GETDATE())) as [Max Requests (10 Mins)],
(select top 1 CurrentRequests from ' + @servernametoken + '.[Symantec_CMDB].[dbo].Evt_NS_Client_Config_Request order by StartTime desc) as [Current Requests],
--(select isnull(datediff(Minute, MIN(createddate),getdate()),0) from ' + @servernametoken + '.[Symantec_CMDB].[dbo].EventQueueEntry) as [Age (min)],
(Select GETDATE()) as [Run At]'

-- 4. Create the Script to execute
-- Uncomment lines in section 4 to match section 2, (should have the same number of @test commands as Servers in section 2)
declare @test as nvarchar(max) = replace(@SQL, @servernametoken, @server1)
--set @test = @test + ' union all ' + replace(@SQL, @servernametoken, @server2)
--set @test = @test + ' union all ' + replace(@SQL, @servernametoken, @server3)
--set @test = @test + ' union all ' + replace(@SQL, @servernametoken, @server4)
--set @test = @test + ' union all ' + replace(@SQL, @servernametoken, @server5)
--set @test = @test + ' union all ' + replace(@SQL, @servernametoken, @server6)
-- if more rows were added in section 2, add more rows here to match

--5. View the command generated, if desired by uncommenting this line
--select @test   -- for testing/troubleshooting, you can see the script being executed

--6. Execute the SQL query
exec sp_executesql @test