{{sidenavigation.sidenavigationExpandLabel}}
{{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
i-net HelpDesk
This application uses cookies to allow login. By continuing to use this application, you agree to the use of cookies.


Help - LOGINventory