/**************************************
Author: Michael S. Fal (http://www.mikefal.net)
Finalized: 2012-04-16

Creates server inventory SQL objects.  Should be created in 
an administrative database ([Admin]).

ChangeLog
2012-04-16 - MSF - FINALIZED
***************************************/

USE [Admin];
go

if exists (select * from sys.tables where name = 'MachineInventory')
 drop table [dbo].[MachineInventory];

CREATE TABLE [dbo].[MachineInventory](
	[ServerName] [varchar](200) NOT NULL,
	[Model] [varchar](100) NULL,
	[Manufacturer] [varchar](100) NULL,
	[Architechture] [varchar](10) NULL,
	[PhysicalCPUS] [int] NULL,
	[LogicalCPUS] [int] NULL,
	[MaxSpeed] [int] NULL,
	[Memory] [int] NULL,
	[OSName] [varchar](100) NULL,
	[OSVersion] [varchar](100) NULL,
	[OSEdition] [varchar](100) NULL,
	[SPVersion] [varchar](10) NULL,
	[Cluster] [varchar](100) NULL,
	[LastModified] [smalldatetime] DEFAULT GETDATE(),
	[LastModifiedUser] [sysname],
 CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED ([ServerName])
);

if exists (select * from sys.tables where name = 'InstanceInventory') 
	drop table [dbo].[InstanceInventory];

CREATE TABLE [dbo].[InstanceInventory](
	[InstanceID] [int] IDENTITY(1,1) NOT NULL,
	[HostName] [varchar](200) NOT NULL,
	[InstanceName] [varchar](200) NULL,
	[SQLName]  AS (case when [InstanceName] IS NULL then [HostName] else ([HostName]+'\')+[InstanceName] end),
	[InstanceOwner] [varchar](200) NULL,
	[SQLVersion] [varchar](20) NULL,
	[SQLVersionDesc] [varchar](20) NULL,
	[SQLEdition] [varchar](50) NULL,
	[IP] [varchar](20) NULL,
	[Port] [int] NULL,
	[MemoryMinMB] [int] NULL,
	[MemoryMaxMB] [int] NULL,
	[MAXDOPVal] [int] NULL,
	[CurrentPhysicalHost] varchar(100) NULL,
	[Enabled] [bit] NULL DEFAULT 1,
	[LastModified] [smalldatetime] NULL DEFAULT GETDATE(),
	[LastModifiedUser] [sysname] NOT NULL DEFAULT SYSTEM_USER,
 CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED ([InstanceID])
);
go

create schema dataload;
go

if exists (select * from sys.tables where name = 'MachineLoad')
 drop table [dataload].[MachineLoad];

CREATE TABLE [dataload].[MachineLoad](
	[ServerName] [varchar](100) NOT NULL,
	[Model] [varchar](100) NULL,
	[Manufacturer] [varchar](100) NULL,
	[Architechture] [varchar](10) NULL,
	[PhysicalCPUS] [int] NULL,
	[LogicalCPUS] [int] NULL,
	[MaxSpeed] [int] NULL,
	[Memory] [int] NULL,
	[OSName] [varchar](100) NULL,
	[OSVersion] [varchar](100) NULL,
	[SPVersion] [varchar](10) NULL,
	[Cluster] [varchar](20) NULL,
 CONSTRAINT [PK_MachineLoad] PRIMARY KEY CLUSTERED ([ServerName])
);

if exists (select * from sys.tables where name = 'InstanceLoad')
 drop table [dataload].[InstanceLoad];

CREATE TABLE [dataload].[InstanceLoad](
	[InstanceID] [int] NOT NULL,
	[SQLVersion] [varchar](20) NULL,
	[SQLVersionDesc] [varchar](20) NULL,
	[SQLEdition] [varchar](50) NULL,
	[IP] [varchar](20) NULL,
	[Port] [varchar](100) NULL,
	[MemoryMinMB] [varchar](100) NULL,
	[MemoryMaxMB] [varchar](100) NULL,
	[MAXDOPVal] [varchar](100) NULL,
	[PhysicalHost] [varchar](100) NULL,
 CONSTRAINT [PK_InstanceLoad] PRIMARY KEY CLUSTERED ([InstanceID])
);

GO

if exists (select * from sys.procedures where name = 'dbasp_ProcessInventory')
 drop procedure dbasp_ProcessInventory;
go

create procedure dbasp_ProcessInventory
as
begin
/**************************************
Author: Michael S. Fal (http://www.mikefal.net)
Finalized: 2012-04-16

Pulls data from inventory staging tables and loads it
into live reporting tables.  Instance inventory is 
updated, machine inventory is deleted and reloaded.

ChangeLog
2012-04-16 - MSF - FINALIZED
***************************************/

update [dbo].[InstanceInventory]
set
	SQLVersion = l.SQlVersion
	,SQLVersionDesc = CASE WHEN l.SQLVersion like '11%' then 'SQL 2012 '
						WHEN l.SQLVersion like '10.50%' then 'SQL 2008 R2 '
						WHEN l.SQLVersion like '10.0%' then 'SQL 2008 '
						WHEN l.SQLVersion like '9%' then 'SQL 2005 '
						WHEN l.SQLVersion like '8%' then 'SQL 2000 ' END + l.SQLVersionDesc
	,SQLEdition = l.SQLEdition
	,IP = l.IP
	,Port = l.Port
	,MemoryMinMB = l.MemoryMinMB
	,MemoryMaxMB = l.MemoryMaxMB
	,MAXDOPVal = l.MAXDOPVal
	,CurrentPhysicalHost = l.PhysicalHost
	,LastModified = GETDATE()
	,LastModifiedUser = SYSTEM_USER
from InstanceInventory i 
	join dataload.InstanceLoad l on (i.InstanceID = l.InstanceID)

delete from MachineInventory
where ServerName in (select ServerName from dataload.MachineLoad)

insert into MachineInventory(ServerName,
					Model,
					Manufacturer,
					Architechture,
					PhysicalCPUS,
					LogicalCPUS,
					MaxSpeed,
					Memory,
					OSName,
					OSVersion,
					OSEdition,
					SPVersion,
					Cluster,
					LastModified,
					LastModifiedUser)
select
	Upper(ServerName),
	Model,
	Manufacturer,
	Architechture,
	PhysicalCPUS,
	case when LogicalCPUS = 0 then PhysicalCPUs else LogicalCPUS end,
	MaxSpeed,
	Memory,
	OSName,
	OSVersion,
	OSName,
	SPVersion,
	Cluster,
	getdate(),
	SYSTEM_USER
from
	dataload.MachineLoad
end; --dbasp_ProcessInventory
