{{getMsg('Help_YouAreHere')}}:
/
{{page.title}}
{{page.title}}
{{$root.getMsg("downLoadHelpAsPdf")}}
{{helpModel.downloadHelpPdfDataStatus}}
LOGINventory
Run the following script on the MS SQL database of LOGINventory. The script creates several additional views. These views are a prerequisite for importing individual asset types, such as computers
, printers
, notebooks
etc. into i-net HelpDesk. The definitions for the asset import are created in the dataimport application.
Note: If you verify the connection in the LOGINventory configuration, then you get a message that 10 unknown tables were found. The following tables (views) are created by the script:
Query name | Description |
---|---|
qryComputers |
Computer details like model, hostname and serial number |
qryMemory |
Memory, including memory capacity and access time |
qryMonitor |
Monitor, including model name and serial number |
qryPrinter |
e.g. printer name, printer driver |
qrySoftwareDatabase |
installed software packages |
qrySoftwareExcel |
MS Excel, among others with version |
qrySoftwareWord |
MS Word, among others with version |
qryWebBrowser |
u.a. browser manufacturer |
qryNetworkAdapter |
network adapter |
Script for 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 for 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