-- CR03 - Invenotry devices by installer evidence USE [FNMSCompliance] GO -- create procedure IF OBJECT_ID ('dbo.custReport_CR03_InventoryDevicesByInstallerEvidence', 'P') IS NOT NULL DROP PROCEDURE dbo.custReport_CR03_InventoryDevicesByInstallerEvidence -- drop Stored Procedure if it exists GO CREATE PROCEDURE custReport_CR03_InventoryDevicesByInstallerEvidence @Filter nvarchar (200) AS DECLARE @Name nvarchar(200) SET @Name = '%' + @Filter + '%' IF (@Name IS NOT NULL AND @Name <> '') BEGIN -- Avoid locking by allowing result to contain uncommitted rows SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Set the language to us_english so the datetime is consistent SET LANGUAGE us_english SELECT TOP 1000000 -- Limit resultset to 1.000.000 records IIE.[ComplianceComputerID] [ComputerID] ,CC.ComputerName [ComputerName] ,REPLACE(REPLACE(CCS.DefaultValue,'[','' ),']','' ) [ComputerStatus] ,CCT.DefaultValue [ComputerType] ,CC.OperatingSystem [OperatingSystem] ,CC.InventoryDate [InventoryDate] ,CC.Manufacturer [Manufacturer] ,CC.ModelNo [ModelNo] ,IEI.[Publisher] [InstallerEvidencePublisher] ,IEI.[DisplayName] [InstallerEvidenceName] ,IEI.[Version] [InstallerEvidenceVersion] ,IEI.[InstallerEvidenceType] [InstallerEvidenceType] ,IIE.[InstallDate] [IntallerEvidenceInstallDate] ,IIE.[DiscoveryDate] [InstallerEvidenceDiscoveryDate] ,STI.Publisher [ApplicationPublisher] ,STI.ProductName [ApplicationProduct] ,STI.SoftwareTitleName [ApplicationName] ,STI.SoftwareTitleVersion [ApplicationVersion] ,STI.EditionName [ApplicationEdition] ,IIE.[InstallerEvidenceID] ,STI.[SoftwareTitleID] FROM [FNMSCompliance].[dbo].[InstalledInstallerEvidence] IIE LEFT JOIN [FNMSCompliance].[dbo].ComplianceComputer CC ON CC.ComplianceComputerID = IIE.ComplianceComputerID LEFT JOIN [FNMSCompliance].[dbo].[ComplianceComputerStatus] CCS ON CCS.ComplianceComputerStatusID = CC.ComplianceComputerStatusID LEFT JOIN [FNMSCompliance].[dbo].[ComplianceComputerType] CCT ON CCT.ComplianceComputerTypeID = CC.ComplianceComputerTypeID LEFT JOIN [FNMSCompliance].[dbo].[InstallerEvidenceInfo] AS IEI ON IEI.InstallerEvidenceID = IIE.InstallerEvidenceID LEFT JOIN [FNMSCompliance].[dbo].[SoftwareTitleInstallerEvidence] STIE ON STIE.InstallerEvidenceID = IEI.InstallerEvidenceID LEFT JOIN [FNMSCompliance].[dbo].[SoftwareTitleInfo] STI ON STI.SoftwareTitleID = STIE.SoftwareTitleID WHERE IEI.[DisplayName] like @Name END GO -- TEST: EXEC dbo.custReport_CR03_InventoryDevicesByInstallerEvidence @Filter = 'UltraEdit%' /* ================================= Report name, visible on WebUI; up to 64 symbols; ================================= */ DECLARE @SearchName nvarchar(64) SET @SearchName = 'CR03 - Inventory devices by Installer evidence' /* ================================= Description of the report, visible on WebUI; up to 1000 symbols; add short instruction how to use the report; ================================= */ DECLARE @Description nvarchar(1000) SET @Description = 'Custom report returning the list of inventory devices by installer evidence. Search on [Installer evidence name]' /* ================================= SQL query or Stored Proceadure ================================= */ DECLARE @SearchSQL nvarchar(1000) SET @SearchSQL = 'EXEC dbo.custReport_CR03_InventoryDevicesByInstallerEvidence @Filter = @SearchText' /* ================================= Connection: Live or DataWarehouse ================================= */ DECLARE @SearchSQLConnection nvarchar(500) SET @SearchSQLConnection = 'Live' /* ================================= Search Mapping (optional); Add aditional functionality on the reports; ================================= */ DECLARE @SearchMapping xml SET @SearchMapping = CAST(' ComputerID false true Computer 1 ComputerName true false Computer 1 ComputerStatus true false Computer 1 ComputerType true false Computer 1 OperatingSystem true false Computer 1 InventoryDate true false Computer 1 Manufacturer false false Computer 1 ModelNo false false Computer 1 InstallerEvidencePublisher true false InstallerEvidence 3 InstallerEvidenceName true false InstallerEvidence 3 InstallerEvidenceVersion true false InstallerEvidence 3 InstallerEvidenceType true false InstallerEvidence 3 IntallerEvidenceInstallDate false false InstallerEvidence 3 InstallerEvidenceDiscoveryDate false false InstallerEvidence 3 InstallerEvidenceID false false InstallerEvidence 3 ApplicationPublisher true false SoftwareTitle 2 ApplicationProduct true false SoftwareTitle 2 ApplicationName true false SoftwareTitle 2 ApplicationVersion true false SoftwareTitle 2 ApplicationEdition true false SoftwareTitle 2 SoftwareTitleID false true SoftwareTitle 2 ' as xml) /* ================================= SearchType; 1 for Custom; Other values are availbale in: [FNMSCompliance].[dbo].[ComplianceSearchType_MT] ================================= */ DECLARE @ComplianceSearchTypeID int SET @ComplianceSearchTypeID = 1 /* ================================= Folder; Values SELECT * FROM [FNMSCompliance].[dbo].[ComplianceSearchFolder_MT] ORDER BY PATH; 4 - root folder ================================= */ DECLARE @ComplianceSearchFolderID int SET @ComplianceSearchFolderID = 4 INSERT INTO [dbo].[ComplianceSavedSearch_MT] ([SearchName] ,[Description] ,[SearchGridLayout] ,[SearchSQL] ,[SearchSQLConnection] ,[SearchMapping] ,[SearchXML] ,[CreatedBy] ,[CreationDate] ,[ModifiedBy] ,[ModificationDate] ,[ComplianceSearchTypeID] ,[ComplianceSearchFolderID] ,[CreatedByOperatorID] ,[RestrictedAccessTypeID] ,[CanDelete] ,[CanChangeMasterObject] ,[ComplianceSavedSearchSystemID] ,[SearchNameResourceName] ,[DescriptionResourceName] ,[SavedSearchLink] ,[SavedSearchFilter] ,[TenantID]) VALUES (@SearchName ,@Description ,NULL -- SearchGridLayout ,@SearchSQL ,@SearchSQLConnection ,@SearchMapping ,NULL -- SearchXML ,'CustomReport' -- CreatedBy ,GETUTCDATE() -- CreationDate ,'CustomReport' -- ModifiedBy ,GETUTCDATE() -- ModificationDate ,@ComplianceSearchTypeID ,@ComplianceSearchFolderID ,NULL -- CreatedByOperatorID ,1 -- RestrictedAccessTypeID ,1 -- CanDelete ,0 -- CanChangeMasterObject ,NULL -- ComplianceSavedSearchSystemID ,NULL -- SearchNameResourceName ,NULL -- DescriptionResourceName ,NULL -- SavedSearchLink ,NULL -- SavedSearchFilter ,1 -- TenantID ) GO