/******************************************************************************/
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
/******************************************************************************/
可以select出每一台電腦的軟硬體嗎?InfoAllData一次就全部記錄，無法分辨哪一台?謝謝~
回覆刪除資料都在 SQL DB 中了，就根據自己的需求撈囉！
刪除