LOGINventory

Führen Sie das nachfolgende Script auf die MS SQL-Datenbank von LOGINventory aus. Das Script legt mehrere zusätzliche Views an. Diese Views sind Voraussetzung für den Import einzelner Asset-Typen, wie Computer, Drucker, Notebooks etc. in den i-net HelpDesk. Die Definitionen für den Asset-Import werden in der Anwendung Datenimport erstellt.

Hinweis: Wenn Sie in der LOGINventory Konfiguration die Verbindung verifizieren, dann erhalten Sie die Meldung, dass 10 unbekannte Tabellen gefunden wurden. Folgende Tabellen (Sichten) werden durch das Script angelegt:

CREATE VIEW qryMemory AS
SELECT          SUM(SIZE) AS MemorySize, Speed, AssetId
FROM            MemoryModuleInfo
GROUP BY AssetId, Speed
GO
 
CREATE VIEW qryComputers AS
SELECT        DeviceUsage.LastUser AS UserName, ComputerAccount.DnsHostName, ComputerAccount.DistinguishedName, AssetModel.Name AS DeviceName, DeviceInfo.ChassisType, 
                         AssetModel.Manufacturer, ComputerAccount.Description, DeviceInfo.SerialNumber, ComputerAccount.OperatingSystemName, ComputerAccount.OperatingSystemVersion, 
                         ComputerAccount.OperatingSystemServicePack, Cpu.Name AS CPU, Asset.Name, CONVERT(VARCHAR, qryMemory.MemorySize) + '/' + CONVERT(VARCHAR, 
                         qryMemory.Speed) AS RAM
FROM            AssetModelInfo INNER JOIN
                         AssetModel ON AssetModelInfo.ItemId = AssetModel.Id INNER JOIN
                         DeviceUsage INNER JOIN
                         Asset ON DeviceUsage.AssetId = Asset.Id INNER JOIN
                         DeviceInfo ON Asset.Id = DeviceInfo.AssetId ON AssetModelInfo.AssetId = Asset.Id LEFT OUTER JOIN
                         qryMemory ON Asset.Id = qryMemory.AssetId LEFT OUTER JOIN
                         ComputerAccount ON Asset.Name = ComputerAccount.Name LEFT OUTER JOIN
                         Cpu INNER JOIN
                         CpuInfo ON Cpu.Id = CpuInfo.ItemId ON Asset.Id = CpuInfo.AssetId
GO
 
CREATE VIEW qryMonitor AS
SELECT        DeviceUsage.LastUser AS UserName, Monitor.Model, Monitor.Name, MonitorInfo.SerialNumber, MonitorInfo.ManufactureWeek
FROM            Monitor INNER JOIN
                         MonitorInfo ON Monitor.Id = MonitorInfo.ItemId INNER JOIN
                         DeviceUsage ON MonitorInfo.AssetId = DeviceUsage.AssetId
GO
 
CREATE VIEW qryPrinter AS
SELECT        DeviceUsage.LastUser AS UserName, PrinterDriver.Name, PrinterInfo.Port, PrinterInfo.[KEY], PrinterInfo.Label, PrinterInfo.[USER]
FROM            PrinterInfo INNER JOIN
                         PrinterDriver ON PrinterInfo.ItemId = PrinterDriver.Id INNER JOIN
                         DeviceUsage ON PrinterInfo.AssetId = DeviceUsage.AssetId
WHERE        (PrinterInfo.Port LIKE 'USB%')
GO
 
CREATE VIEW qrySoftwareDatabase AS
SELECT        DeviceUsage.LastUser AS UserName, SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, 
                         SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId
WHERE        (SoftwarePackage.Name LIKE 'Microsoft SQL Server % Setup (%') OR
                         (SoftwarePackage.Name LIKE 'MySQL Server%') OR
                         (SoftwarePackage.Name LIKE 'Oracle Database%')
GO
 
CREATE VIEW qrySoftwareExcel AS
SELECT        DeviceUsage.LastUser AS UserName, SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, 
                         SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Excel%')
GO
 
CREATE VIEW qrySoftwareWord AS
SELECT        DeviceUsage.LastUser AS UserName, SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, 
                         SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Word%')
GO
 
CREATE VIEW qryWebBrowser AS
SELECT        DeviceUsage.LastUser AS UserName, SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, 
                         SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId
WHERE        (SoftwarePackage.Name LIKE 'Google Chrome') OR
                         (SoftwarePackage.Name LIKE 'Microsoft Internet Explorer%') OR
                         (SoftwarePackage.Name LIKE 'Mozilla Firefox%') OR
                         (SoftwarePackage.Name LIKE 'Safari') OR (SoftwarePackage.Name LIKE 'Opera %')
GO
 
CREATE VIEW qryNetworkAdapter AS
SELECT        NetworkAdapterInfo.Ip, NetworkAdapter.Name, NetworkAdapterInfo.Mac, NetworkAdapterInfo.DefaultGateway, NetworkAdapterInfo.Speed, 
                         NetworkAdapterInfo.DnsDomain, NetworkAdapterInfo.DnsServer, DeviceUsage.LastUser AS UserName, NetworkAdapterInfo.AssetId
FROM            NetworkAdapterInfo INNER JOIN
                         NetworkAdapter ON NetworkAdapterInfo.ItemId = NetworkAdapter.Id INNER JOIN
                         DeviceUsage ON NetworkAdapterInfo.AssetId = DeviceUsage.AssetId
GO

CREATE VIEW qryMemory AS
SELECT          SUM(SIZE) AS MemorySize, Speed, AssetId
FROM            MemoryModuleInfo
GROUP BY AssetId, Speed
GO
 
CREATE VIEW qryComputers AS
SELECT        ComputerAccount.DnsHostName, ComputerAccount.DistinguishedName, AssetModel.Name AS DeviceName, DeviceInfo.ChassisType, AssetModel.Manufacturer, ComputerAccount.Description, DeviceInfo.SerialNumber, 
                         ComputerAccount.OperatingSystemName, ComputerAccount.OperatingSystemVersion, ComputerAccount.OperatingSystemServicePack, Cpu.Name AS CPU, Asset.Name, CONVERT(VARCHAR, qryMemory.MemorySize) 
                         + '/' + CONVERT(VARCHAR, qryMemory.Speed) AS RAM, [USER].Name AS UserName
FROM            AssetModelInfo INNER JOIN
                         AssetModel ON AssetModelInfo.ItemId = AssetModel.Id INNER JOIN
                         DeviceUsage INNER JOIN
                         Asset ON DeviceUsage.AssetId = Asset.Id INNER JOIN
                         DeviceInfo ON Asset.Id = DeviceInfo.AssetId ON AssetModelInfo.AssetId = Asset.Id INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id LEFT OUTER JOIN
                         qryMemory ON Asset.Id = qryMemory.AssetId LEFT OUTER JOIN
                         ComputerAccount ON Asset.Name = ComputerAccount.Name LEFT OUTER JOIN
                         Cpu INNER JOIN
                         CpuInfo ON Cpu.Id = CpuInfo.ItemId ON Asset.Id = CpuInfo.AssetId
GO
 
CREATE VIEW qryMonitor AS
SELECT        Monitor.Model, Monitor.Name, MonitorInfo.SerialNumber, MonitorInfo.ManufactureWeek, [USER].Name AS UserName
FROM            Monitor INNER JOIN
                         MonitorInfo ON Monitor.Id = MonitorInfo.ItemId INNER JOIN
                         DeviceUsage ON MonitorInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
GO
 
CREATE VIEW qryPrinter AS
SELECT        PrinterDriver.Name, PrinterInfo.Port, PrinterInfo.[KEY], PrinterInfo.Label, PrinterInfo.[USER], [USER].Name AS UserName
FROM            PrinterInfo INNER JOIN
                         PrinterDriver ON PrinterInfo.ItemId = PrinterDriver.Id INNER JOIN
                         DeviceUsage ON PrinterInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
WHERE        (PrinterInfo.Port LIKE 'USB%')
GO
 
CREATE VIEW qrySoftwareDatabase AS
SELECT        SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText, 
                         [USER].Name AS UserName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft SQL Server % Setup (%') OR
                         (SoftwarePackage.Name LIKE 'MySQL Server%') OR
                         (SoftwarePackage.Name LIKE 'Oracle Database%')
GO
 
CREATE VIEW qrySoftwareExcel AS
SELECT        SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText, 
                         [USER].Name AS UserName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Excel%')
GO
 
CREATE VIEW qrySoftwareWord AS
SELECT        SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText, [USER].Id, 
                         [USER].Name AS UserName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Word%')
GO
 
CREATE VIEW qryWebBrowser AS
SELECT        SoftwarePackage.Name AS Productname, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackage.Version, SoftwarePackage.Publisher, SoftwarePackageInfo.InstallDateText, 
                         [USER].Name AS UserName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
WHERE        (SoftwarePackage.Name LIKE 'Google Chrome') OR
                         (SoftwarePackage.Name LIKE 'Microsoft Internet Explorer%') OR
                         (SoftwarePackage.Name LIKE 'Mozilla Firefox%') OR
                         (SoftwarePackage.Name LIKE 'Safari') OR
                         (SoftwarePackage.Name LIKE 'Opera %')
GO
 
CREATE VIEW qryNetworkAdapter AS
SELECT        NetworkAdapterInfo.Ip, NetworkAdapter.Name, NetworkAdapterInfo.Mac, NetworkAdapterInfo.DefaultGateway, NetworkAdapterInfo.Speed, NetworkAdapterInfo.DnsDomain, NetworkAdapterInfo.DnsServer, 
                         NetworkAdapterInfo.AssetId, DeviceUsage.LastUserId, [USER].Name AS UserName
FROM            NetworkAdapterInfo INNER JOIN
                         NetworkAdapter ON NetworkAdapterInfo.ItemId = NetworkAdapter.Id INNER JOIN
                         DeviceUsage ON NetworkAdapterInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [USER] ON DeviceUsage.LastUserId = [USER].Id
GO

CREATE VIEW qryMemory AS
SELECT          SUM(MemoryModuleInfo.Size) AS MemorySize, MemoryModuleInfo.Speed, [MemoryModuleInfo].AssetId,
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, SUBSTRING([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            MemoryModuleInfo INNER JOIN
                         DeviceUsage ON MemoryModuleInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON MemoryModuleInfo.AssetId = [Device].Id
GROUP BY MemoryModuleInfo.Speed, [MemoryModuleInfo].AssetId, [USER].Id, [User].Name, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)), [Device].Id, [Device].DnsHostName, [Device].LegacyId
GO
 
Create VIEW qryComputers AS
SELECT AssetModel.Name AS DeviceName, DeviceInfo.ChassisType, AssetModel.Manufacturer, Cpu.Name AS CPU, Asset.Name, CONVERT(VARCHAR, qryMemory.MemorySize) + '/' + CONVERT(VARCHAR, 
                  qryMemory.Speed) AS RAM, SUBSTRING([User].Name, CHARINDEX('\', [User].Name) + 1, LEN([User].Name) - CHARINDEX('\', [User].Name)) AS UserName, HardwareAsset.SerialNumber, 
                  OperatingSystemInfo.DistinguishedName, OperatingSystem.Name AS OperatingSystemName, OperatingSystemInfo.FullVersion AS OperatingSystemVersion, 
                  OperatingSystemInfo.ServicePack AS OperatingSystemServicePack
FROM     OperatingSystem INNER JOIN
                  OperatingSystemInfo ON OperatingSystem.Id = OperatingSystemInfo.ItemId INNER JOIN
                  AssetModelInfo INNER JOIN
                  AssetModel ON AssetModelInfo.ItemId = AssetModel.Id INNER JOIN
                  DeviceUsage INNER JOIN
                  Asset ON DeviceUsage.AssetId = Asset.Id INNER JOIN
                  DeviceInfo ON Asset.Id = DeviceInfo.AssetId ON AssetModelInfo.AssetId = Asset.Id INNER JOIN
                  [User] ON DeviceUsage.LastUserId = [User].Id INNER JOIN
                  HardwareAsset ON Asset.Id = HardwareAsset.Id ON OperatingSystemInfo.AssetId = Asset.Id LEFT OUTER JOIN
                  qryMemory ON Asset.Id = qryMemory.AssetId LEFT OUTER JOIN
                  Cpu INNER JOIN
                  CpuInfo ON Cpu.Id = CpuInfo.ItemId ON Asset.Id = CpuInfo.AssetId
WHERE  (DeviceUsage.Archived = 0) AND (Asset.Archived IS NULL)
GO
 
Create VIEW qrySoftwareAccess AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Access%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Access%')
GO
 
Create VIEW qrySoftwareExcel AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Excel%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Excel%')
GO
 
Create VIEW qrySoftwarePowerPoint AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office PowerPoint%') OR
             (SoftwarePackage.Name LIKE 'Microsoft PowerPoint%')
GO
 
Create VIEW qrySoftwareWord AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Word%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Word%')
GO
 
Create VIEW qrySoftwareVisio AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Visio%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Visio%')
GO
 
Create VIEW qrySoftwareOutlook AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Outlook%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Outlook%')
GO
 
Create VIEW qrySoftwareTeams AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft Office Teams%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Teams%')
GO
 
Create VIEW qrySoftwareDatabase AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Microsoft SQL Server % Setup (%') OR
             (SoftwarePackage.Name LIKE 'MySQL Server%') OR
             (SoftwarePackage.Name LIKE 'Oracle Database%')
GO
 
Create VIEW qrySoftwareWebBrowser AS
SELECT        SoftwarePackage.Publisher, SoftwarePackage.Name AS Productname, SoftwarePackage.Version, SoftwarePackageInfo.InstallLocation, SoftwarePackageInfo.InstallDate, SoftwarePackageInfo.InstallDateText, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            SoftwarePackage INNER JOIN
                         SoftwarePackageInfo ON SoftwarePackage.Id = SoftwarePackageInfo.ItemId INNER JOIN
                         DeviceUsage ON SoftwarePackageInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON SoftwarePackageInfo.AssetId = [Device].Id
WHERE        (SoftwarePackage.Name LIKE 'Google Chrome') OR
             (SoftwarePackage.Name LIKE 'Edge%') OR
             (SoftwarePackage.Name LIKE 'Microsoft Internet Explorer%') OR
             (SoftwarePackage.Name LIKE 'Mozilla Firefox%') OR
             (SoftwarePackage.Name LIKE 'Safari') OR
             (SoftwarePackage.Name LIKE 'Opera %')
GO
 
 
 
Create VIEW qryPrinter AS
SELECT        PrinterDriver.Name, PrinterInfo.Label, PrinterInfo.[Key], PrinterInfo.Port,
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            PrinterDriver INNER JOIN
                         PrinterInfo ON PrinterDriver.Id = PrinterInfo.ItemId INNER JOIN
                         DeviceUsage ON PrinterInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON PrinterInfo.AssetId = [Device].Id
WHERE        (PrinterInfo.Port is null) OR
             (PrinterInfo.Port LIKE '10.%') OR
             (PrinterInfo.Port LIKE 'Lan%') OR
             (PrinterInfo.Port LIKE 'LPT%') OR
             (PrinterInfo.Port LIKE 'USB%')
GO
 
Create VIEW qryMonitor AS
SELECT        Monitor.Vendor, Monitor.Name, Monitor.Model, Monitor.Size, MonitorInfo.SerialNumber, MonitorInfo.ManufactureWeek, 
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            Monitor INNER JOIN
                         MonitorInfo ON Monitor.Id = MonitorInfo.ItemId INNER JOIN
                         DeviceUsage ON MonitorInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON MonitorInfo.AssetId = [Device].Id
GO
 
Create VIEW qryNetworkAdapter AS
SELECT        NetworkAdapter.Name, NetworkAdapterInfo.Mac, NetworkAdapterInfo.Ip, NetworkAdapterInfo.SubnetMask, NetworkAdapterInfo.DefaultGateway, NetworkAdapterInfo.DnsServer, NetworkAdapterInfo.Speed,
                       [USER].Id AS USERID, [USER].Name AS FQDNUserName, substring([USER].Name,	charindex('\', [USER].Name)+1,	len([USER].Name)-charindex('\', [USER].Name)) UserName,
                       [Device].Id AS ComputerID, [Device].DnsHostName AS FQDNComputerName, [Device].LegacyId AS ComputerName
FROM            NetworkAdapter INNER JOIN
                         NetworkAdapterInfo ON NetworkAdapter.Id = NetworkAdapterInfo.ItemId INNER JOIN
                         DeviceUsage ON NetworkAdapterInfo.AssetId = DeviceUsage.AssetId INNER JOIN
                         [User] ON DeviceUsage.LastUserId = [USER].Id INNER JOIN
                         [Device] ON NetworkAdapterInfo.AssetId = [Device].Id
GO