Video Screencast Help

Inventory summery customized report

Created: 30 May 2013
planetmanu's picture

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';



                i.[Name][Computer Name],


    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%' 



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%')


                CONVERT (VARCHAR(40),c.[Collection Time],120) DESC


Operating Systems: