chettyrajesh

Hi, Have modified th eSQL query to exclude IPv6 also if you have multiple IPv4 IP's then it will com in one single row. SELECT v_R_System.Netbios_Name0, CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE 'vmware%' THEN 'VM' ELSE v_GS_PC_BIOS.SerialNumber0 END AS [serial No], v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor], CASE WHEN v_GS_PROCESSOR.ADDRESSWIDTH0 = '32' THEN '32 Bit' ELSE '64 Bit' END AS [Processor Type], CASE WHEN v_GS_PROCESSOR.IS64BIT0 = '1' THEN 'Yes' ELSE 'No' END AS [64 Bit Supported], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)], c.IPAddress AS [iP Address], v_GS_Operating_System.Caption0 AS [Operating system], v_GS_Operating_System.Version0 AS [Operating system version], v_GS_OPERATING_SYSTEM.ServicePackMajorVersion0, CONVERT(nvarchar(10), v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On], v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0 FROM v_R_System INNER JOIN (SELECT t1.resourceid AS rsid2, IPAddress = substring ((SELECT (IP_Addresses0 + ', ') FROM v_RA_System_IPAddresses t2 WHERE t2.IP_Addresses0 NOT LIKE '169%' AND IP_Addresses0 NOT LIKE '0.%' AND IP_Addresses0 NOT LIKE '%::%' AND IP_Addresses0 NOT LIKE '192.%' AND t1.resourceid = t2.resourceid ORDER BY resourceid FOR xml path('')), 1, 50000) FROM v_RA_System_IPAddresses t1 GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0, v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0, v_R_System.AD_Site_Name0