Fixes issue with PowerBi Performance Report that does not display many of the charts from an FCI instance

[asavioliMSFT]

When data is collected from a FCI, the memory report fails as shown below:

It seems issue is with the following query. To fix it, we should use property ComputerNamePhysicalNetBIOS instead of MachineName to assign the value to the variable @MachineName

DECLARE @RoundToMinutes SMALLINT = 0 , @StartDate DATETIME = '1/1/1900 12:00:00 AM' , @EndDate DATETIME = '1/1/2099 12:00:00 AM'

DECLARE @MachineName VARCHAR(30); DECLARE @InstanceName VARCHAR(30);

SELECT @MachineName = '\' + PropertyValue FROM dbo.tbl_ServerProperties WHERE PropertyName = 'MachineName';

SELECT @InstanceName = PropertyValue FROM dbo.tbl_ServerProperties WHERE PropertyName = 'InstanceName';

IF OBJECT_ID('dbo.CounterData') IS NOT NULL AND OBJECT_ID('dbo.CounterDetails') IS NOT NULL BEGIN / Memory Perfmon Counters / WITH cteCounterData AS ( SELECT CASE WHEN @RoundToMinutes < 1 THEN CAST(dat.CounterDateTime AS DATETIME) ELSE DATEADD(MINUTE, (DATEPART(MINUTE, CAST(dat.CounterDateTime AS DATETIME)) / @RoundToMinutes) * @RoundToMinutes, DATEADD(HOUR, DATEDIFF(HOUR, 0, CAST(dat.CounterDateTime AS DATETIME)), 0)) END AS SampleDateTime , det.InstanceName , CAST(dat.CounterValue AS DECIMAL(38, 2)) AS CounterValue , det.ObjectName , det.CounterName FROM dbo.CounterData AS dat INNER JOIN dbo.CounterDetails AS det ON dat.CounterID = det.CounterID WHERE det.MachineName = @MachineName AND ( ( @InstanceName IS NULL AND det.ObjectName LIKE 'SQLServer:Memory Manager%' ) OR ( @InstanceName IS NOT NULL AND det.ObjectName LIKE 'MSSQL$' + @InstanceName + ':Memory Manager%' ) ) AND ( det.CounterName IN ('Memory Grants Pending', 'Target Server Memory (KB)', 'Total Server Memory (KB)') --OR ( -- det.CounterName IN ('Working Set') -- AND det.InstanceName LIKE '%sqlservr%' -- ) ) AND dat.CounterDateTime >= @StartDate AND dat.CounterDateTime <= @EndDate ) , cteCounterDataSummarized AS ( SELECT SampleDateTime , InstanceName , MAX(CounterValue) AS MaxCounterValue , MIN(CounterValue) AS MinCounterValue , AVG(CounterValue) AS AvgCounterValue , ObjectName , CounterName FROM cteCounterData GROUP BY SampleDateTime , InstanceName , ObjectName , CounterName ) SELECT SampleDateTime , InstanceName , MaxCounterValue , MinCounterValue , AvgCounterValue , ObjectName , CounterName FROM cteCounterDataSummarized ORDER BY SampleDateTime , InstanceName; END; ELSE BEGIN SELECT TOP 0 NULL AS SampleDateTime , NULL AS InstanceName , NULL AS MaxCounterValue , NULL AS MinCounterValue , NULL AS AvgCounterValue , NULL AS ObjectName , NULL AS CounterName; END;

[PiJoCoder]

Commit - 8cbf841d4a6d50c8b714f9a167e06910837d7371

[PiJoCoder]

Fix in PR - https://github.com/microsoft/SqlNexus/pull/242