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

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ConfigurationManagement.SP.GetComputerDetails.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_GetComputerDetails' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetComputerDetails


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetComputerDetails


	@ComputerDimKey	int = 0,


	@LanguageCode	varchar(256) = 'ENU'


	


	


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


  


  Select 


C.ComputerDimKey,


C.BaseManagedEntityId,


C.PrincipalName,


C.DNSName,


C.DomainDnsName, 


C.NetbiosComputerName, 


C.NetbiosDomainName,


C.IPAddress, 


C.ActiveDirectoryObjectSid,


C.ActiveDirectorySite, 


C.IsVirtualMachine,


C.LastInventoryDate,


C.OffsetInMinuteFromGreenwichTime,





PC.ChassisType,


PC.HardwareId,


PC.MACAddresses,


PC.Manufacturer,


PC.Model,


PC.NumberOfProcessors,


PC.PlatformType,


PC.ISA,


PC.SerialNumber,


PC.SMBIOSAssetTag,


PC.SystemType,





OS.BuildNumber AS OS_BuildNumber,


OS.CountryCode AS OS_CountryCode,


OS.CSDVersion AS OS_CSDVersion,


OS.Description AS OS_Description,


OS.DisplayName AS OS_DisplayName,


OS.InstallDate AS OS_InstallDate,


OS.LogicalProcessors AS OS_LogicalProcessors,


OS.MajorVersion AS OS_MajorVersion,


OS.Manufacturer AS OS_Manufacturer,


OS.MinorVersion AS OS_MinorVersion,


OS.OSLanguage AS OS_OSLanguage,


OS.Locale AS OS_Locale,


OS.OSVersion AS OS_OSVersion,


OS.OSVersionDisplayName AS OS_OSVersionDisplayName,


OS.PhysicalMemory AS OS_PhysicalMemory, 


OS.ProductType AS OS_ProductType, 


OS.SerialNumber AS OS_SerialNumber, 


OS.SystemDrive AS OS_SystemDrive, 


OS.WindowsDirectory AS OS_WindowsDirectory, 


OS.ServicePackVersion AS  OS_ServicePackVersion,





PrimaryUser.UserName PrimaryUser_UserName,


PrimaryUser.DisplayName PrimaryUser_DisplayName


from 


dbo.ComputerDimvw C


left outer join 


dbo.DeployedComputerRunsWindowsComputerFactvw WCOPC 


	on WCOPC.DeployedComputerRunsWindowsComputer_ComputerDimKey = C.ComputerDimKey


	and WCOPC.DeletedDate is null


left outer join 


dbo.ComputerHasPrimaryUserFactvw CPUFct


	on CPUFct.ComputerDimKey = C.ComputerDimKey


	and CPUFct.DeletedDate is null


left outer join 


dbo.UserDimvw PrimaryUser


	on PrimaryUser.UserDimKey = CPUFct.ComputerPrimaryUser_UserDimKey


	and PrimaryUser.IsDeleted = 0


left outer join 


dbo.DeployedComputerDimvw PC


	on PC.DeployedComputerDimKey = WCOPC.DeployedComputerDimKey


left outer join 


dbo.ComputerHostsOperatingSystemFactvw WCHOS


	on WCHOS.ComputerDimKey = C.ComputerDimKey


	and WCHOS.DeletedDate is null


left outer join 


dbo.OperatingSystemDimvw OS


	on WCHOS.ComputerHostsOperatingSystem_OperatingSystemDimKey = OS.OperatingSystemDimKey





WHERE


C.ComputerDimKey = @ComputerDimKey


          


          SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO








GRANT EXECUTE ON dbo.ServiceManager_Report_ConfigurationManagement_SP_GetComputerDetails TO reportuser


GO