Skip to main content

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