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;