Hi All,
I have stuck up at a inventory summery customized report. We have done the report customization however the dropdown field is giving multiple entries and we are not able to assign filter selection for that report.
I am using follwoing query and I have attached print screen of the same
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
drop table dbo.temp_cd;
drop table dbo.temp_dvd;
drop table dbo.temp_hdd;
select * into dbo.temp_cd from dbo.Inv_HW_Storage where dbo.Inv_HW_Storage.[Interface Type] = 'CD ROM';
select * into dbo.temp_dvd
from dbo.Inv_HW_Storage where dbo.Inv_HW_Storage.[Interface Type] = 'DVD ROM';
select * into dbo.temp_hdd
from dbo.Inv_HW_Storage where dbo.Inv_HW_Storage.[Interface Type] = 'IDE Hard Disk';
SELECT DISTINCT
i.[Guid],
i.[Name][Computer Name],
i.Domain,
comp.[OS Name] , comp.[Last Logon User] , comp.[Hardware Serial Number] , tcp.[IP Address], monitor.[Serial Number] , cd.[Interface Type] , dvd.[Interface Type],
sn1.Manufacturer [Manufacturer],
sn1.[Model] [Computer Model],
ram.[Total Physical Memory (Bytes)]/ (1024 * 1024) as 'RAM in MB', pro.[Max Clock Speed (Mega-hertz)],
processor.[Processor Name] , bios.[Release Date] , hdd.[Max Media Size (Kilobytes)]/ (1024 * 1024 ) as 'hdd size(in GB)'
FROM dbo.vComputer i
JOIN dbo.Inv_Inventory_Results c
ON c.[_ResourceGuid] = i.Guid
JOIN dbo.Inv_AeX_AC_Identification d
ON d.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = d.[_ResourceGuid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
left join dbo.Inv_AeX_AC_Identification comp
on comp.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.Inv_AeX_AC_TCPIP tcp
on tcp.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.Inv_HW_Desktop_Monitor monitor
on monitor.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.Inv_HW_Computer_System ram
on ram.[_ResourceGuid] = c.[_ResourceGuid]
left join [dbo].[Inv_HW_Processor] pro
on pro.[_ResourceGuid] = c.[_ResourceGuid]
left join [dbo].[Inv_HW_Processor_Name_Windows] processor
on processor.[_ResourceGuid] = c.[_ResourceGuid]
left join [dbo].[Inv_SW_BIOS_Element] bios
on bios.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.temp_cd cd
on cd.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.temp_dvd dvd
on dvd.[_ResourceGuid] = c.[_ResourceGuid]
left join dbo.temp_hdd hdd
on hdd.[_ResourceGuid] = c.[_ResourceGuid]
WHERE c.Agent = 'Inventory Agent'
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
/*
ORDER BY
CONVERT (VARCHAR(40),c.[Collection Time],120) DESC
*/
/* d.[System Type] LIKE 'Win%'
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND d.Domain LIKE '%Domain%'
AND lower (it.[Guid]) LIKE lower ('%Filter%')
AND d.[System Type] LIKE '%OS Platform%'
AND lower (it.[Guid]) LIKE lower ('%Filter%')
ORDER BY
CONVERT (VARCHAR(40),c.[Collection Time],120) DESC
*/