ServiceManager.Report.ConfigurationManagement.SP.ComputerInventoryGetData.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.ConfigurationManagement.SP.ComputerInventoryGetData.Install" Accessibility="Public" FileName="ServiceManager.Report.ConfigurationManagement.SP.ComputerInventoryGetData.Install.sql"/>

File Content: ServiceManager.Report.ConfigurationManagement.SP.ComputerInventoryGetData.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- Drop stored procedure if it already exists


IF EXISTS (


  SELECT * 


    FROM INFORMATION_SCHEMA.ROUTINES 


   WHERE SPECIFIC_SCHEMA = N'dbo'


     AND SPECIFIC_NAME = N'ServiceManager_Report_ConfigurationManagement_SP_ComputerInventoryGetData' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_ComputerInventoryGetData


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_ComputerInventoryGetData


 @NetBIOSComputerName nvarchar(256) = NULL,


 @DNSName nvarchar(256) = NULL,


 @IsVirtual bit = NULL,


 @DomainDNSName nvarchar(256) = '!<All>!',


 @OSVersionName nvarchar(256) = '!<All>!',


 @OSVersion nvarchar(256) = '!<All>!',


 @Manufacturer nvarchar(256) = '!<All>!',


 @Model nvarchar(256) = '!<All>!'


	


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


  


  DECLARE @tableDomainDNSName TABLE (value nvarchar(256))  


  INSERT @tableDomainDNSName (value)


  SELECT * FROM dbo.fn_CSVToTableString(@DomainDNSName)


  


  DECLARE @tableOSVersionName TABLE (value nvarchar(256))  


  INSERT @tableOSVersionName(value)


  SELECT * FROM dbo.fn_CSVToTableString(@OSVersionName)


  


  DECLARE @tableOSVersion TABLE (value nvarchar(256))  


  INSERT @tableOSVersion (value)


  SELECT * FROM dbo.fn_CSVToTableString(@OSVersion)


  


  DECLARE @tableManufacturer TABLE (value nvarchar(256))  


  INSERT @tableManufacturer(value)


  SELECT * FROM dbo.fn_CSVToTableString(@Manufacturer)


  


  DECLARE @tableModel TABLE (value nvarchar(256))  


  INSERT @tableModel (value)


  SELECT * FROM dbo.fn_CSVToTableString(@Model)


  


 


SELECT


C.ComputerDimKey,


C.IsVirtualMachine,


C.NetbiosComputerName,


C.DomainDnsName,


C.DNSName,


C.IPAddress,


C.IsDeleted,





PC.Manufacturer,


PC.Model,





OS.OSVersionDisplayName,


OS.BuildNumber AS OSVersion


FROM


dbo.ComputerDimvw C





INNER JOIN @tableDomainDNSName tempDNS ON


	('!<All>!' = tempDNS.value OR C.DomainDnsName = tempDNS.value)





LEFT OUTER JOIN


dbo.ComputerHostsOperatingSystemFactvw CHOSFact


ON C.ComputerDimKey = CHOSFact.ComputerDimKey


AND CHOSFact.DeletedDate IS NULL





LEFT OUTER JOIN


dbo.OperatingSystemDimvw OS


ON CHOSFact.ComputerHostsOperatingSystem_OperatingSystemDimKey = OS.OperatingSystemDimKey





INNER JOIN @tableOSVersionName tempOSVerName ON


	('!<All>!' = tempOSVerName.value OR OS.OSVersionDisplayName = tempOSVerName.value)





INNER JOIN @tableOSVersion tempOSVer ON


	('!<All>!' = tempOSVer.value OR OS.BuildNumber = tempOSVer.value)





LEFT OUTER JOIN


dbo.DeployedComputerRunsWindowsComputerFactvw WCOPCFact


ON C.ComputerDimKey = WCOPCFact.DeployedComputerRunsWindowsComputer_ComputerDimKey


and CHOSFact.DateKey = WCOPCFact.DateKey


and WCOPCFact.DeletedDate IS NULL





LEFT OUTER JOIN


dbo.DeployedComputerDimvw PC


ON WCOPCFact.DeployedComputerDimKey = PC.DeployedComputerDimKey





INNER JOIN @tableManufacturer tempMfc ON


	('!<All>!' = tempMfc.value OR PC.Manufacturer = tempMfc.value)





INNER JOIN @tableModel tempMod ON


	('!<All>!' = tempMod.value OR PC.Model = tempMod.value)





WHERE


((@NetBIOSComputerName IS NULL) OR (NetbiosComputerName LIKE @NetBIOSComputerName) ) AND


((@DNSName IS NULL) OR (DNSName LIKE @DNSName)) AND


(


                (@IsVirtual IS NULL) OR 


                (ISNULL(C.IsVirtualMachine, 0) = @IsVirtual)


)


ORDER BY C.NetbiosComputerName


          


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ConfigurationManagement_SP_ComputerInventoryGetData TO reportuser


GO