Report Is empty

[mehrarpit]

Hello,

Report is empty. Does it take time to show any data. Please help.

Attaching screenshot of same.

[gkieffer83]

I think this was written to work with a default instance. I had the same issue when monitoring a named instance. So I've rewritten the code of the stored proc and this fixed it:

CREATE PROCEDURE spGetPerfCountersFromPowerShell AS BEGIN

DECLARE @syscounters NVARCHAR(4000)
SET @syscounters=STUFF((SELECT DISTINCT ''',''' +LTRIM([counter_name])
FROM [dba_local].[dbo].[PerformanceCounterList]
WHERE [is_captured_ind] = 1 FOR XML PATH('')), 1, 2, '')+'''' 

DECLARE @cmd NVARCHAR(4000)
DECLARE @syscountertable TABLE (id INT IDENTITY(1,1), [output] VARCHAR(500))
DECLARE @syscountervaluestable TABLE (id INT IDENTITY(1,1), [value] VARCHAR(500))

SET @cmd = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe "& get-counter -counter '+ @syscounters +' | Select-Object -ExpandProperty Readings"'
INSERT @syscountertable
EXEC master..xp_cmdshell @cmd

declare @sqlnamedinstance sysname
declare @networkname sysname
if (select CHARINDEX('\',@@SERVERNAME)) = 0
begin
INSERT [dba_local].[dbo].[PerformanceCounter] (CounterName, CounterValue, DateSampled)
SELECT  REPLACE(REPLACE(REPLACE(ct.[output],'\\'+@@SERVERNAME+'\',''),' :',''),'sqlserver:','')[CounterName] , CONVERT(varchar(20),ct2.[output]) [CounterValue], GETDATE() [DateSampled]
FROM @syscountertable ct
LEFT OUTER JOIN (
SELECT id - 1 [id], [output]
FROM @syscountertable
WHERE PATINDEX('%[0-9]%', LEFT([output],1)) > 0  
) ct2 ON ct.id = ct2.id
WHERE  ct.[output] LIKE '\\%'
ORDER BY [CounterName] ASC
end
else
begin
select @networkname=RTRIM(left(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME) - 1))
select @sqlnamedinstance=RIGHT(@@SERVERNAME,CHARINDEX('\',REVERSE(@@SERVERNAME))-1)
INSERT [dba_local].[dbo].[PerformanceCounter] (CounterName, CounterValue, DateSampled)
SELECT  REPLACE(REPLACE(REPLACE(ct.[output],'\\'+@networkname+'\',''),' :',''),'mssql$'+@sqlnamedinstance+':','')[CounterName] , CONVERT(varchar(20),ct2.[output]) [CounterValue], GETDATE() [DateSampled]
FROM @syscountertable ct
LEFT OUTER JOIN (
SELECT id - 1 [id], [output]
FROM @syscountertable
WHERE PATINDEX('%[0-9]%', LEFT([output],1)) > 0  
) ct2 ON ct.id = ct2.id
WHERE  ct.[output] LIKE '\\%'
ORDER BY [CounterName] ASC
end

END ';

[mehrarpit]

Hello @gkieffer83 ,

Thanks. But still the same issue persist.

[agnaldocarmo]

for me, show me empty report on reporting service 2016, and works right on vnext ctp 1.4

[savjani-zz]

@mehrarpit - Is the Jobs running successfully. Do you see the data populated in the dba_local database? @agnaldocarmo - I havent heard of issue with reporting service 2016. Curious, have u applied the latest SP and CU for SQL Server 2016 (SQL 2016 Sp1 CU2) ?

[DiHo78]

Keep in mind: If you just installed the solution and run the Report immediately it won't work. The @ enddate paramter Looks like EndDate='2017-05-10 00:00:00' and if you only have values after 00:00:00 those won't get selected. So if you wait one day there should be something in the Report.

[mehrarpit]

Thank You. It is working now. It is not working because of the following reason.

1)spGetPerfCountersFromPowerShell proc is created to run for default instance only. I have made change suggested change by @gkieffer83 . 2)@DiHo78 correctly suggested that you have to wait for one day to see data into the report.

Thanks once again ;)