2016-10-24

Software List (Enhanced Version) Create DB View


/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[ASMA]    Script Date: 10/24/2016 10:29:40 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ASMA]'))
DROP VIEW [dbo].[ASMA]
GO

/****** Object:  View [dbo].[ASMA]    Script Date: 10/24/2016 10:29:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[ASMA]
AS
SELECT     FFF.Serial, FFF.UserName, FFF.DeviceID, FFF.DeviceIDSerial, FFF.DeviceUserAgent, FFF.DeviceFriendlyName, FFF.DeviceModel, FFF.DeviceType,
                      ASMAData_5.Value AS DeviceMACAddress
FROM         (SELECT     EEE.Serial, EEE.UserName, EEE.DeviceID, EEE.DeviceIDSerial, EEE.DeviceUserAgent, EEE.DeviceFriendlyName, EEE.DeviceModel,
                                              ASMAData_1.Value AS DeviceType, EEE.DeviceMACAddress
                       FROM          (SELECT     DDD.Serial, DDD.UserName, DDD.DeviceID, DDD.DeviceIDSerial, DDD.DeviceUserAgent, DDD.DeviceFriendlyName,
                                                                      ASMAData_2.Value AS DeviceModel, DDD.DeviceType, DDD.DeviceMACAddress
                                               FROM          (SELECT     CCC.Serial, CCC.UserName, CCC.DeviceID, CCC.DeviceIDSerial, CCC.DeviceUserAgent,
                                                                                              ASMAData_3.Value AS DeviceFriendlyName, CCC.DeviceModel, CCC.DeviceType, CCC.DeviceMACAddress
                                                                       FROM          (SELECT     BBB.Serial, BBB.UserName, BBB.DeviceID, BBB.DeviceIDSerial, ASMAData_4.Value AS DeviceUserAgent,
                                                                                                                       BBB.DeviceFriendlyName, BBB.DeviceModel, BBB.DeviceType, BBB.DeviceMACAddress
                                                                                               FROM          (SELECT     AAA.Serial, AAA.UserName, ASMAData_5.Value AS DeviceID,
                                                                                                                                              ASMAData_5.Serial AS DeviceIDSerial, AAA.DeviceUserAgent, AAA.DeviceFriendlyName,
                                                                                                                                              AAA.DeviceModel, AAA.DeviceType, AAA.DeviceMACAddress
                                                                                                                       FROM          (SELECT     dbo.ASMALink.Serial, dbo.ASMAData.Value AS UserName, dbo.ASMALink.DeviceID,
                                                                                                                                                                      dbo.ASMALink.DeviceUserAgent, dbo.ASMALink.DeviceFriendlyName,
                                                                                                                                                                      dbo.ASMALink.DeviceModel, dbo.ASMALink.DeviceType,
                                                                                                                                                                      dbo.ASMALink.DeviceMACAddress
                                                                                                                                               FROM          dbo.ASMALink INNER JOIN
                                                                                                                                                                      dbo.ASMAData ON dbo.ASMAData.Type = 'UserName' AND
                                                                                                                                                                      dbo.ASMAData.Serial = dbo.ASMALink.UserName) AS AAA INNER JOIN
                                                                                                                                              dbo.ASMAData AS ASMAData_5 ON ASMAData_5.Type = 'DeviceID' AND
                                                                                                                                              ASMAData_5.Serial = AAA.DeviceID) AS BBB INNER JOIN
                                                                                                                      dbo.ASMAData AS ASMAData_4 ON ASMAData_4.Type = 'DeviceUserAgent' AND
                                                                                                                      ASMAData_4.Serial = BBB.DeviceUserAgent) AS CCC INNER JOIN
                                                                                              dbo.ASMAData AS ASMAData_3 ON ASMAData_3.Type = 'DeviceFriendlyName' AND
                                                                                              ASMAData_3.Serial = CCC.DeviceFriendlyName) AS DDD INNER JOIN
                                                                      dbo.ASMAData AS ASMAData_2 ON ASMAData_2.Type = 'DeviceModel' AND ASMAData_2.Serial = DDD.DeviceModel)
                                              AS EEE INNER JOIN
                                              dbo.ASMAData AS ASMAData_1 ON ASMAData_1.Type = 'DeviceType' AND ASMAData_1.Serial = EEE.DeviceType) AS FFF INNER JOIN
                      dbo.ASMAData AS ASMAData_5 ON ASMAData_5.Type = 'DeviceMACAddress' AND ASMAData_5.Serial = FFF.DeviceMACAddress

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoAllData]    Script Date: 03/23/2016 17:25:28 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoAllData]'))
DROP VIEW [dbo].[InfoAllData]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoAllData]    Script Date: 03/23/2016 17:25:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoAllData]
AS
SELECT     dbo.InfoAllDataLink.Serial, Table_A.Type, dbo.InfoAllDataLink.Value
FROM         dbo.InfoAllDataLink LEFT OUTER JOIN
                          (SELECT     Serial AS DataTypeSerial, Type
                            FROM          dbo.InfoAllDataType) AS Table_A ON Table_A.DataTypeSerial = dbo.InfoAllDataLink.Type

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoCDROM]    Script Date: 03/23/2016 17:25:36 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoCDROM]'))
DROP VIEW [dbo].[InfoCDROM]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoCDROM]    Script Date: 03/23/2016 17:25:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoCDROM]
AS
SELECT     Table_A.Serial, Table_B.Caption, Table_C.MediaType, Table_D.MfrAssignedRevisionLevel, Table_E.Name
FROM         (SELECT     Serial, Caption, MediaType, MfrAssignedRevisionLevel, Name
                       FROM          dbo.InfoCDROMLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Caption
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'CDROMCaption')) AS Table_B ON Table_A.Caption = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MediaType
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'CDROMMediaType')) AS Table_C ON Table_A.MediaType = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MfrAssignedRevisionLevel
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'CDROMMfrAssignedRevisionLevel')) AS Table_D ON Table_A.MfrAssignedRevisionLevel = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Name
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'CDROMName')) AS Table_E ON Table_A.Name = Table_E.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoCPU]    Script Date: 03/23/2016 17:25:43 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoCPU]'))
DROP VIEW [dbo].[InfoCPU]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoCPU]    Script Date: 03/23/2016 17:25:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoCPU]
AS
SELECT     Table_A.Serial, Table_B.AddressWidth, Table_C.Architecture, Table_D.Caption, Table_E.DataWidth, Table_F.DeviceID, Table_G.ExtClock,
                      Table_H.Family, Table_I.[Level], Table_J.Manufacturer, Table_K.MaxClockSpeed, Table_L.Name, Table_M.NumberOfCores,
                      Table_N.NumberOfLogicalProcessors, Table_O.ProcessorId, Table_P.SocketDesignation
FROM         (SELECT     Serial, AddressWidth, Architecture, Caption, DataWidth, DeviceID, ExtClock, Family, [Level], Manufacturer, MaxClockSpeed, Name,
                                              NumberOfCores, NumberOfLogicalProcessors, ProcessorId, SocketDesignation
                       FROM          dbo.InfoCPULink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS AddressWidth
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'CPUAddressWidth')) AS Table_B ON Table_A.AddressWidth = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Architecture
                            FROM          dbo.InfoAllData AS InfoAllData_14
                            WHERE      (Type = 'CPUArchitecture')) AS Table_C ON Table_A.Architecture = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Caption
                            FROM          dbo.InfoAllData AS InfoAllData_13
                            WHERE      (Type = 'CPUCaption')) AS Table_D ON Table_A.Caption = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DataWidth
                            FROM          dbo.InfoAllData AS InfoAllData_12
                            WHERE      (Type = 'CPUDataWidth')) AS Table_E ON Table_A.DataWidth = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DeviceID
                            FROM          dbo.InfoAllData AS InfoAllData_11
                            WHERE      (Type = 'CPUDeviceID')) AS Table_F ON Table_A.DeviceID = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ExtClock
                            FROM          dbo.InfoAllData AS InfoAllData_10
                            WHERE      (Type = 'CPUExtClock')) AS Table_G ON Table_A.ExtClock = Table_G.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Family
                            FROM          dbo.InfoAllData AS InfoAllData_9
                            WHERE      (Type = 'CPUFamily')) AS Table_H ON Table_A.Family = Table_H.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS [Level]
                            FROM          dbo.InfoAllData AS InfoAllData_8
                            WHERE      (Type = 'CPULevel')) AS Table_I ON Table_A.[Level] = Table_I.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Manufacturer
                            FROM          dbo.InfoAllData AS InfoAllData_7
                            WHERE      (Type = 'CPUManufacturer')) AS Table_J ON Table_A.Manufacturer = Table_J.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MaxClockSpeed
                            FROM          dbo.InfoAllData AS InfoAllData_6
                            WHERE      (Type = 'CPUMaxClockSpeed')) AS Table_K ON Table_A.MaxClockSpeed = Table_K.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Name
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'CPUName')) AS Table_L ON Table_A.Name = Table_L.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS NumberOfCores
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'CPUNumberOfCores')) AS Table_M ON Table_A.NumberOfCores = Table_M.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS NumberOfLogicalProcessors
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'CPUNumberOfLogicalProcessors')) AS Table_N ON Table_A.NumberOfLogicalProcessors = Table_N.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProcessorId
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'CPUProcessorId')) AS Table_O ON Table_A.ProcessorId = Table_O.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS SocketDesignation
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'CPUSocketDesignation')) AS Table_P ON Table_A.SocketDesignation = Table_P.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoDisk]    Script Date: 03/23/2016 17:25:48 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoDisk]'))
DROP VIEW [dbo].[InfoDisk]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoDisk]    Script Date: 03/23/2016 17:25:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoDisk]
AS
SELECT     Table_A.Serial, Table_B.Caption, Table_C.FirmwareRevision, Table_D.InterfaceType, Table_E.MediaType, Table_F.Model, Table_G.SerialNumber,
                      Table_H.Signature, Table_I.Size
FROM         (SELECT     Serial, Caption, FirmwareRevision, InterfaceType, MediaType, Model, SerialNumber, Signature, Size
                       FROM          dbo.InfoDiskLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Caption
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'DiskCaption')) AS Table_B ON Table_A.Caption = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS FirmwareRevision
                            FROM          dbo.InfoAllData AS InfoAllData_7
                            WHERE      (Type = 'DiskFirmwareRevision')) AS Table_C ON Table_A.FirmwareRevision = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS InterfaceType
                            FROM          dbo.InfoAllData AS InfoAllData_6
                            WHERE      (Type = 'DiskInterfaceType')) AS Table_D ON Table_A.InterfaceType = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MediaType
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'DiskMediaType')) AS Table_E ON Table_A.MediaType = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Model
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'DiskModel')) AS Table_F ON Table_A.Model = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS SerialNumber
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'DiskSerialNumber')) AS Table_G ON Table_A.SerialNumber = Table_G.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Signature
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'DiskSignature')) AS Table_H ON Table_A.Signature = Table_H.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Size
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'DiskSize')) AS Table_I ON Table_A.Size = Table_I.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoMB]    Script Date: 03/23/2016 17:25:58 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoMB]'))
DROP VIEW [dbo].[InfoMB]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoMB]    Script Date: 03/23/2016 17:25:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoMB]
AS
SELECT     Table_A.Serial, Table_B.Manufacturer, Table_C.Product, Table_D.SerialNumber, Table_E.Version
FROM         (SELECT     Serial, Manufacturer, Product, SerialNumber, Version
                       FROM          dbo.InfoMBLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Manufacturer
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'MBManufacturer')) AS Table_B ON Table_A.Manufacturer = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Product
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'MBProduct')) AS Table_C ON Table_A.Product = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS SerialNumber
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'MBSerialNumber')) AS Table_D ON Table_A.SerialNumber = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Version
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'MBVersion')) AS Table_E ON Table_A.Version = Table_E.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoMEM]    Script Date: 03/23/2016 17:26:03 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoMEM]'))
DROP VIEW [dbo].[InfoMEM]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoMEM]    Script Date: 03/23/2016 17:26:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoMEM]
AS
SELECT     Table_A.Serial, Table_B.BankLabel, Table_C.DeviceLocator, Table_D.Tag, Table_E.Capacity, Table_F.Manufacturer, Table_G.MemoryType,
                      Table_H.PartNumber, Table_I.SerialNumber, Table_J.Speed
FROM         (SELECT     Serial, BankLabel, DeviceLocator, Tag, Capacity, Manufacturer, MemoryType, PartNumber, SerialNumber, Speed
                       FROM          dbo.InfoMEMLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS BankLabel
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'MEMBankLabel')) AS Table_B ON Table_A.BankLabel = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DeviceLocator
                            FROM          dbo.InfoAllData AS InfoAllData_8
                            WHERE      (Type = 'MEMDeviceLocator')) AS Table_C ON Table_A.DeviceLocator = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Tag
                            FROM          dbo.InfoAllData AS InfoAllData_7
                            WHERE      (Type = 'MEMTag')) AS Table_D ON Table_A.Tag = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Capacity
                            FROM          dbo.InfoAllData AS InfoAllData_6
                            WHERE      (Type = 'MEMCapacity')) AS Table_E ON Table_A.Capacity = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Manufacturer
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'MEMManufacturer')) AS Table_F ON Table_A.Manufacturer = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MemoryType
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'MEMMemoryType')) AS Table_G ON Table_A.MemoryType = Table_G.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS PartNumber
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'MEMPartNumber')) AS Table_H ON Table_A.PartNumber = Table_H.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS SerialNumber
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'MEMSerialNumber')) AS Table_I ON Table_A.SerialNumber = Table_I.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Speed
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'MEMSpeed')) AS Table_J ON Table_A.Speed = Table_J.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoNIC]    Script Date: 03/23/2016 17:26:09 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoNIC]'))
DROP VIEW [dbo].[InfoNIC]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoNIC]    Script Date: 03/23/2016 17:26:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoNIC]
AS
SELECT     Table_A.Serial, Table_B.DHCPEnabled, Table_C.DNSHostName, Table_D.IPAddress, Table_E.Caption, Table_F.MACAddress, Table_G.ServiceName,
                      Table_A.Registed
FROM         (SELECT     Serial, DHCPEnabled, DNSHostName, IPAddress, Caption, MACAddress, ServiceName, Registed
                       FROM          dbo.InfoNICLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DHCPEnabled
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'NICDHCPEnabled')) AS Table_B ON Table_A.DHCPEnabled = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DNSHostName
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'NICDNSHostName')) AS Table_C ON Table_A.DNSHostName = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS IPAddress
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'NICIPAddress')) AS Table_D ON Table_A.IPAddress = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Caption
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'NICCaption')) AS Table_E ON Table_A.Caption = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS MACAddress
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'NICMACAddress')) AS Table_F ON Table_A.MACAddress = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ServiceName
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'NICServiceName')) AS Table_G ON Table_A.ServiceName = Table_G.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoOS]    Script Date: 03/23/2016 17:26:15 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoOS]'))
DROP VIEW [dbo].[InfoOS]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoOS]    Script Date: 03/23/2016 17:26:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoOS]
AS
SELECT     Table_A.Serial, Table_B.CSName, Table_C.RegisteredUser, Table_D.SerialNumber, Table_E.InstallDate, Table_F.LastBootUpTime,
                      Table_G.BuildNumber, Table_H.Caption, Table_I.CodeSet, Table_J.CountryCode, Table_K.CSDVersion, Table_L.CurrentTimeZone, Table_M.Locale,
                      Table_N.Manufacturer, Table_O.OperatingSystemSKU, Table_P.OSArchitecture, Table_Q.OSProductSuite, Table_R.OSLanguage, Table_S.OSType,
                      Table_T.OtherTypeDescription, Table_U.ProductKey, Table_V.ProductType, Table_W.Version
FROM         (SELECT     Serial, CSName, RegisteredUser, SerialNumber, InstallDate, LastBootUpTime, BuildNumber, Caption, CodeSet, CountryCode, CSDVersion,
                                              CurrentTimeZone, Locale, Manufacturer, OperatingSystemSKU, OSArchitecture, OSProductSuite, OSLanguage, OSType,
                                              OtherTypeDescription, ProductKey, ProductType, Version
                       FROM          dbo.InfoOSLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS CSName
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'OSCSName')) AS Table_B ON Table_A.CSName = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS RegisteredUser
                            FROM          dbo.InfoAllData AS InfoAllData_21
                            WHERE      (Type = 'OSRegisteredUser')) AS Table_C ON Table_A.RegisteredUser = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS SerialNumber
                            FROM          dbo.InfoAllData AS InfoAllData_20
                            WHERE      (Type = 'OSSerialNumber')) AS Table_D ON Table_A.SerialNumber = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS InstallDate
                            FROM          dbo.InfoAllData AS InfoAllData_19
                            WHERE      (Type = 'OSInstallDate')) AS Table_E ON Table_A.InstallDate = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS LastBootUpTime
                            FROM          dbo.InfoAllData AS InfoAllData_18
                            WHERE      (Type = 'OSLastBootUpTime')) AS Table_F ON Table_A.LastBootUpTime = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS BuildNumber
                            FROM          dbo.InfoAllData AS InfoAllData_17
                            WHERE      (Type = 'OSBuildNumber')) AS Table_G ON Table_A.BuildNumber = Table_G.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Caption
                            FROM          dbo.InfoAllData AS InfoAllData_16
                            WHERE      (Type = 'OSCaption')) AS Table_H ON Table_A.Caption = Table_H.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS CodeSet
                            FROM          dbo.InfoAllData AS InfoAllData_15
                            WHERE      (Type = 'OSCodeSet')) AS Table_I ON Table_A.CodeSet = Table_I.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS CountryCode
                            FROM          dbo.InfoAllData AS InfoAllData_14
                            WHERE      (Type = 'OSCountryCode')) AS Table_J ON Table_A.CountryCode = Table_J.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS CSDVersion
                            FROM          dbo.InfoAllData AS InfoAllData_13
                            WHERE      (Type = 'OSCSDVersion')) AS Table_K ON Table_A.CSDVersion = Table_K.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS CurrentTimeZone
                            FROM          dbo.InfoAllData AS InfoAllData_12
                            WHERE      (Type = 'OSCurrentTimeZone')) AS Table_L ON Table_A.CurrentTimeZone = Table_L.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Locale
                            FROM          dbo.InfoAllData AS InfoAllData_11
                            WHERE      (Type = 'OSLocale')) AS Table_M ON Table_A.Locale = Table_M.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Manufacturer
                            FROM          dbo.InfoAllData AS InfoAllData_10
                            WHERE      (Type = 'OSManufacturer')) AS Table_N ON Table_A.Manufacturer = Table_N.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OperatingSystemSKU
                            FROM          dbo.InfoAllData AS InfoAllData_9
                            WHERE      (Type = 'OSOperatingSystemSKU')) AS Table_O ON Table_A.OperatingSystemSKU = Table_O.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OSArchitecture
                            FROM          dbo.InfoAllData AS InfoAllData_8
                            WHERE      (Type = 'OSOSArchitecture')) AS Table_P ON Table_A.OSArchitecture = Table_P.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OSProductSuite
                            FROM          dbo.InfoAllData AS InfoAllData_7
                            WHERE      (Type = 'OSOSProductSuite')) AS Table_Q ON Table_A.OSProductSuite = Table_Q.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OSLanguage
                            FROM          dbo.InfoAllData AS InfoAllData_6
                            WHERE      (Type = 'OSOSLanguage')) AS Table_R ON Table_A.OSLanguage = Table_R.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OSType
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'OSOSType')) AS Table_S ON Table_A.OSType = Table_S.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OtherTypeDescription
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'OSOtherTypeDescription')) AS Table_T ON Table_A.OtherTypeDescription = Table_T.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProductKey
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'OSProductKey')) AS Table_U ON Table_A.ProductKey = Table_U.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProductType
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'OSProductType')) AS Table_V ON Table_A.ProductType = Table_V.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Version
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'OSVersion')) AS Table_W ON Table_A.Version = Table_W.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoOffice]    Script Date: 03/23/2016 17:26:21 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoOffice]'))
DROP VIEW [dbo].[InfoOffice]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoOffice]    Script Date: 03/23/2016 17:26:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoOffice]
AS
SELECT     Table_A.Serial, Table_B.Version, Table_C.ProductName, Table_D.ProductID, Table_E.OfficeType, Table_F.ProductKey, Table_G.Note
FROM         (SELECT     Serial, Version, ProductName, ProductID, OfficeType, ProductKey, Note
                       FROM          dbo.InfoOfficeLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Version
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'OfficeVersion')) AS Table_B ON Table_A.Version = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProductName
                            FROM          dbo.InfoAllData AS InfoAllData_5
                            WHERE      (Type = 'OfficeProductName')) AS Table_C ON Table_A.ProductName = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProductID
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'OfficeProductID')) AS Table_D ON Table_A.ProductID = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS OfficeType
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'OfficeType')) AS Table_E ON Table_A.OfficeType = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ProductKey
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'OfficeProductKey')) AS Table_F ON Table_A.ProductKey = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Note
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'OfficeNote')) AS Table_G ON Table_A.Note = Table_G.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoPrivilege]    Script Date: 03/23/2016 17:26:27 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoPrivilege]'))
DROP VIEW [dbo].[InfoPrivilege]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoPrivilege]    Script Date: 03/23/2016 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoPrivilege]
AS
SELECT     Table_A.Serial, Table_B.PrivilegeType, Table_C.Account
FROM         (SELECT     Serial, PrivilegeType, Account
                       FROM          dbo.InfoPrivilegeLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS PrivilegeType
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'PrivilegeType')) AS Table_B ON Table_A.PrivilegeType = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Account
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'PrivilegeAccount')) AS Table_C ON Table_A.Account = Table_C.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoRecord]    Script Date: 03/23/2016 17:26:33 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoRecord]'))
DROP VIEW [dbo].[InfoRecord]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoRecord]    Script Date: 03/23/2016 17:26:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoRecord]
AS
SELECT     Table_A.Serial, Table_B.DateTime, Table_C.HostName, Table_D.DataType, Table_E.LastLogonAccount, Table_F.ComputerSID
FROM         (SELECT     Serial, DateTime, HostName, DataType, LastLogonAccount, ComputerSID
                       FROM          dbo.InfoRecordLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DateTime
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'RecordDateTime')) AS Table_B ON Table_A.DateTime = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS HostName
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'RecordHostName')) AS Table_C ON Table_A.HostName = Table_C.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS DataType
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'RecordDataType')) AS Table_D ON Table_A.DataType = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS LastLogonAccount
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'RecordLastLogonAccount')) AS Table_E ON Table_A.LastLogonAccount = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS ComputerSID
                            FROM          dbo.InfoAllData AS InfoAllData_4
                            WHERE      (Type = 'RecordComputerSID')) AS Table_F ON Table_A.ComputerSID = Table_F.Serial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[InfoSoftware]    Script Date: 03/24/2016 10:04:17 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InfoSoftware]'))
DROP VIEW [dbo].[InfoSoftware]
GO

USE [Info]
GO

/****** Object:  View [dbo].[InfoSoftware]    Script Date: 03/24/2016 10:04:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InfoSoftware]
AS
SELECT     Table_A.Serial, Table_B.InstallDate, Table_C.DisplayNameSerial, Table_C.DisplayName, Table_D.DisplayVersionSerial, Table_D.DisplayVersion,
                      Table_E.PublisherSerial, Table_E.Publisher
FROM         (SELECT     Serial, InstallDate, DisplayName, DisplayVersion, Publisher
                       FROM          dbo.InfoSoftwareLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, Value AS InstallDate
                            FROM          dbo.InfoAllData
                            WHERE      (Type = 'SoftwareInstallDate')) AS Table_B ON Table_A.InstallDate = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial AS DisplayNameSerial, Value AS DisplayName
                            FROM          dbo.InfoAllData AS InfoAllData_3
                            WHERE      (Type = 'SoftwareDisplayName')) AS Table_C ON Table_A.DisplayName = Table_C.DisplayNameSerial LEFT OUTER JOIN
                          (SELECT     Serial AS DisplayVersionSerial, Value AS DisplayVersion
                            FROM          dbo.InfoAllData AS InfoAllData_2
                            WHERE      (Type = 'SoftwareDisplayVersion')) AS Table_D ON Table_A.DisplayVersion = Table_D.DisplayVersionSerial LEFT OUTER JOIN
                          (SELECT     Serial AS PublisherSerial, Value AS Publisher
                            FROM          dbo.InfoAllData AS InfoAllData_1
                            WHERE      (Type = 'SoftwarePublisher')) AS Table_E ON Table_A.Publisher = Table_E.PublisherSerial

GO

/******************************************************************************/

USE [Info]
GO

/****** Object:  View [dbo].[OutgoingProcess]    Script Date: 10/24/2016 10:33:56 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OutgoingProcess]'))
DROP VIEW [dbo].[OutgoingProcess]
GO

/****** Object:  View [dbo].[OutgoingProcess]    Script Date: 10/24/2016 10:33:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[OutgoingProcess]
AS
SELECT     Table_A.Serial, Table_B.DateTime, Table_C.OutgoingProcessHostName, Table_I.HostName, Table_C.OutgoingProcessHostNameSerial, Table_D.NIC,
                      Table_E.[To], Table_F.Port, Table_G.ProcessSerial, Table_G.Process, Table_I.InfoSerial
FROM         (SELECT     Serial, DateTime, HostName, NIC, [To], Port, Process
                       FROM          dbo.OutgoingLink) AS Table_A LEFT OUTER JOIN
                          (SELECT     Serial, CONVERT(datetime, Value, 120) AS DateTime
                            FROM          dbo.OutgoingData
                            WHERE      (Type = 'DateTime')) AS Table_B ON Table_A.DateTime = Table_B.Serial LEFT OUTER JOIN
                          (SELECT     Serial AS OutgoingProcessHostNameSerial, Value AS OutgoingProcessHostName
                            FROM          dbo.OutgoingData AS OutgoingData_5
                            WHERE      (Type = 'HostName')) AS Table_C ON Table_A.HostName = Table_C.OutgoingProcessHostNameSerial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS NIC
                            FROM          dbo.OutgoingData AS OutgoingData_4
                            WHERE      (Type = 'NIC')) AS Table_D ON Table_A.NIC = Table_D.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS [To]
                            FROM          dbo.OutgoingData AS OutgoingData_3
                            WHERE      (Type = 'To')) AS Table_E ON Table_A.[To] = Table_E.Serial LEFT OUTER JOIN
                          (SELECT     Serial, Value AS Port
                            FROM          dbo.OutgoingData AS OutgoingData_2
                            WHERE      (Type = 'Port')) AS Table_F ON Table_A.Port = Table_F.Serial LEFT OUTER JOIN
                          (SELECT     Serial AS ProcessSerial, Value AS Process
                            FROM          dbo.OutgoingData AS OutgoingData_1
                            WHERE      (Type = 'Process')) AS Table_G ON Table_A.Process = Table_G.ProcessSerial LEFT OUTER JOIN
                          (SELECT     MAX(Serial) AS InfoSerial, HostName
                            FROM          dbo.InfoRecord
                            GROUP BY HostName) AS Table_I ON UPPER(Table_I.HostName) = Table_C.OutgoingProcessHostName

GO

/******************************************************************************/

2 則留言:

  1. 可以select出每一台電腦的軟硬體嗎?InfoAllData一次就全部記錄,無法分辨哪一台?謝謝~

    回覆刪除
    回覆
    1. 資料都在 SQL DB 中了,就根據自己的需求撈囉!

      刪除