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 Web-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:

Name der Abfrage Beschreibung
qryComputers Computer-Details wie Modell, Hostname und Seriennummer
qryMemory Arbeitsspeicher, u.a. Speicherkapazität und Zugriffszeit
qryMonitor Monitor, u.a. Model-Name und Seriennummer
qryPrinter u.a. Druckername, Druckertreiber
qrySoftwareDatabase installierte Software-Pakete
qrySoftwareExcel MS Excel, u.a. mit Version
qrySoftwareWord MS Word, u.a. mit Version
qryWebBrowser u.a. Browser-Hersteller
qryNetworkAdapter Netzwerkadapter

Script für LOGINventory 7

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

Script für LOGINventory 8

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