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