SQL query examples (deprecated)

For SQL query examples, see Query.

Query to SQL

Here we will show how you can convert a query written in the internal query language into SQL.
Original query:

SELECT AS "User"=[User].@Node,[Computer].[Computer Type],[Computer].@Node,[Monitor].@Node COUNT 
WHERE @Class='User' 
WITH 
    SELECT AS [Computer] WHERE @Class='Computer' AND ([Computer Type] = "desktop"),
    SELECT AS [Monitor] WHERE @Class='Monitor' 
ENDWITH

This query will show us the users' objects, their desktop type computers and monitors. It will also show us the total number of monitors under all users (regardless of computer type). When converting to SQL, we will proceed by first finding all the undeleted User type objects and their sub-objects of the Computer type.

SELECT [User].[Object name] AS [User NodeName], Node.[Object name] AS [Computer Name] FROM [Query].[ObjectEnu] [User]
LEFT JOIN [Query].NodeParent ON [User].[Object id] = NodeParent.ParentNodeId
LEFT JOIN [Query].[ObjectEnu] [Node] ON NodeParent.ChildNodeId = Node.[Object id]
WHERE [User].[object type] = N'User' AND (Node.[Object type] = N'Computer/desktop' OR Node.[Object type] = N'Monitor')

Note:

Unlike the original query in the query language, the resulting SQL query does not contain the total number of monitors. This value needs to be found in a separate query.

Conversion of standard EPQ queries

The following SQL queries display the same data as the standard EPQ files.

Inventory by room

Objects with the inventory number property, grouped by the Room property and sorted by network name and class.

SELECT 
    [Object name],
    [Object type],
    [Inventory number],
    [Room],
    [User],
    [Network branch name] 
FROM Query.ObjectEnu 
WHERE [Inventory number] IS NOT NULL

Note: Then sort the query result in the table by the "Room" column.

Inventory by user

Objects with the inventory number property, grouped by the User property.

SELECT 
    [Object name],
    [Object type],
    [Inventory number],
    [Room],
    [User],
    [Network branch name] 
FROM Query.ObjectEnu 
WHERE [Inventory number] IS NOT NULL

Note: Then sort the query result in the table by the column "User".

Uninstalled software by software

Installed software, grouped by computer.

SELECT c.[Object name] AS [Computer], Product AS [Application],
Installed, LicenseInventoryNumber AS [Computer inventory number],
u.[Object name] AS [User], LicenseInvoiceNumber AS [License document number],
LicenceName AS [LicenseName] FROM Query.Software
LEFT JOIN Query.ObjectEnu c ON c.[Object id] = Software.ComputerNodeId 
LEFT JOIN Query.ObjectEnu u ON u.[Object id] = Software.[User]
WHERE Product like N'ALVAO%'

Operating systems

Installed OS, grouped by computer.

SELECT Computer.NodeName AS [Computer], Product AS [OS], Installed AS [Installation Date],
Computer.[Inventory Number] AS [Inventory Number], [Computer].UserNodeId AS [User], LicenseInvoiceNumber AS [Document], [LicenceName] AS [Assigned license], LicenseActivationKey AS [OS - serial number] 
FROM Query.Software 
LEFT JOIN Query.Computer ON Computer.NodeId = Software.ComputerNodeId WHERE Category = 1

Computers

Computers, their sub-objects and properties.

SELECT [Object name], [Network branch name], [User], [Purchase date], [Inventory number], [Serial number],
[Warranty expiration], [Total hard disk capacity (GB)] AS [HDD], CPU, [Operating system] 
FROM Query.ObjectEnu
WHERE [Object of Computer type] = 'Yes'

Changes by date

List of events in the log from the specified parameters. Grouping by objects.

SELECT [Object name], [Object type], [Record date (UTC)], [Title], [Message]
FROM [Query].[ObjectLogEnu]
LEFT JOIN [Query].[ObjectEnu] ON [ObjectEnu].[Object id] = [ObjectLogEnu].[Object id]

Object moves by date

List of movement events in the log. Grouping by date.

SELECT [Object name], [Object type], [Record date (UTC)], [Title], [Message]
FROM [Query].[ObjectLogEnu]
LEFT JOIN [Query].[ObjectEnu] ON [ObjectEnu].[Object id] = [ObjectLogEnu].[Object id]
WHERE ObjectLogEnu.[Title] = N'Move' AND ObjectLogEnu.[Record kind] = 'Object history'

Equipment by date of purchase

List of objects by date of purchase.

SELECT [Object name], CONVERT(datetime, [Purchase date]) AS [Date], [Room], [User]
FROM Query.ObjectEnu

Equipment under warranty

List of objects according to their warranty (property).

SELECT [Object name], CONVERT(datetime, [Purchase date]) AS [Date], [Room], [User], [Warranty expiration]
FROM Query.ObjectEnu

Did not find what you were looking for? Ask our technical support team.