Skip Navigation LinksALVAO 10.2ALVAO Asset ManagementSystem Implementation in an OrganizationCustom Edits and ExtensionsSQL Queries Skip Navigation Links. Skip Navigation Links Skip Navigation Links.


Change ALVAO version
Change language

SQL Queries

General Tips

  1. When creating the SQL queries we recommend to use the views created for easier access to data in the Asset Management. All the views designed for access to information from the AM have the database scheme "Query."
  2. SQL queries allow to run only SELECT queries.
  3. Always enter the custom column names in the View.[Column] format, for example Node.[Serial number]. You can rename the columns using the AS command, for example SELECT View.[Column] AS [Column View].
  4. Don't use SELECT * FROM in the queries, always name specific columns, otherwise displaying of results may be slowed.
  5. Views containing the object properties always contain 100 most used object properties. You can update those views (newly define 100 most used properties) in the ALVAO WebApp – Management – Database maintenance – Refresh data for reports
  6. Prior to creating custom queries we recommend to look at examples of views usage described below.

SQL Query Samples

Below we provide some basic SQL queries to find data on objects.

Display all computers in the active registry with similar information

Details about all computers may be found in the following query.

SELECT [Object name], [RAM size (GB)], [Total hard disk capacity (GB)], [Processor], [Operating system], [Video card], [IP addresses], [MAC addresses] FROM Query.ObjectCsy WHERE [Computer object] = 'Yes'

Display all computers in the active registry with AcrobatReader installed

Here we join the values from multiple views into one using the LEFT JOIN operator.
SELECT ObjectCsy.[Object name], Software.Product FROM Query.ObjectCsy LEFT JOIN Query.Software ON [Software].ComputerNodeId = ObjectCsy.[Object ID] WHERE Software.Product = 'Adobe Acrobat Reader'

Display all computer objects and the history of their moving

A query for computer objects and some selected items from their history.
SELECT ObjectCsy.[Object name], ObjectLogCsy.[Record date (UTC)], ObjectLogCsy.[Title], ObjectLogCsy.[Report] FROM Query.ObjectCsy LEFT JOIN Query.ObjectLogCsy ON ObjectLogCsy.[Object ID] = ObjectCsy.[Object ID] WHERE [Computer object] = 'Yes' AND ObjectLogCsy.[Title] = N'Transfer' AND ObjectLogCsy.[Record type] = 'Object history'

Disk drives and free space on them

Querying computer disk drives and free disk space.

SELECT o.[Object name], d.[Logic disc], d.[Capacity (GB)], d.[Free space (GB)], d.[Free space (%)] FROM Query.DiskDriveCsy d LEFT JOIN [Query].[ObjectCsy] o ON d.[Object ID]=o.[Object ID]

Converting query language into SQL – displaying all the user objects, their desktop type computers, and the total number of their monitors

We will demonstrate how to transform a query in the query language into SQL.
The original query:
SELECT AS "User"=[User] [User].@Node,[Computer].[Computer kind],[Computer].@Node,[Monitor].@Node COUNT WHERE @Class='User' WITH SELECT AS [Computer] WHERE @Class='Computer' AND ([Computer kind] = "desktop"), SELECT AS [Monitor] WHERE @Class='Monitor' ENDWITH
This query will display the user objects, their desktop kind computers, and monitors. It also displays the total number of monitors under all users (no matter the computer type). When converting to SQL we will proceed in this way: first we will find all the User type objects that have not been deleted and their Computer type sub-objects.
SELECT [User].[Object name] AS [User NodeName], Node.[Object name] AS [Computer Name] FROM [Query].[ObjectCsy] [User] LEFT JOIN [Query].NodeParent ON [User].[Object ID] = NodeParent.ParentNodeId LEFT JOIN [Query].[ObjectCsy] [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 final SQL query doesn't contain the total number of monitors. This value has to be found in an individual query.

Converting the standard EPQ queries

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

Assets by rooms

Objects with the Inventory number property grouped by the Room property and ordered by name in the network and by class.
SELECT [Object name] , [Object type], [Inventory number], [Room], [User], [Name on the network] FROM Query.ObjectCsy WHERE [Inventory number] IS NOT NULL

Note: Sort the query result in the table according to the "Room" column.

Assets by users

Objects with the Inventory number property grouped by the User property.
SELECT [Object name] , [Object type], [Inventory number], [Room], [User], [Name on the network] FROM Query.ObjectCsy WHERE [Inventory number] IS NOT NULL

Note: Sort the query result in the table according to the "User" column.

Installed software by software

Installed software grouped by computers.
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 [License name] FROM Query.Software LEFT JOIN Query.ObjectCsy c ON c.[Object ID] = Software.ComputerNodeId LEFT JOIN Query.ObjectCsy u ON u.[Object ID] = Software.[User] WHERE Product like N'ALVAO%'

Operating systems

Installed OS grouped by computers.
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], [Name on the network], [User], [Purchase date], [Inventory number], [Serial number], [Warranty expiration], [Total hard disk capacity (GB)] AS [HDD], [Processor], [Operating system] FROM Query.ObjectCsy WHERE [Computer type] = 'Yes'

Overview of interventions by date

List of events saved in the Log since the parameters entered. Grouping by objects.
SELECT [Object name], [Object type], [Record date (UTC)], [Title], [Report] FROM [Query].[ObjectLogCsy] LEFT JOIN [Query].[ObjectCsy] ON [ObjectCsy].[Object ID] = [ObjectLogCsy].[Object ID]

Object movements by date

List of move events saved in the Log. Grouping by date.
SELECT [Object name], [Object type], [Record date (UTC)], [Title], [Report] FROM [Query].[ObjectLogCsy] LEFT JOIN [Query].[ObjectCsy] ON [ObjectCsy].[Object ID] = [ObjectLogCsy].[Object ID] WHERE ObjectLogCsy.[Title] = N'Transfer' AND ObjectLogCsy.[Record kind] = 'Object history'

Devices by date of purchase

Objects list by purchase date.
SELECT [Object name], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User] FROM Query.ObjectCsy

Devices by warranty

Objects list by their warranty (property):
SELECT [Object name], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User],[Warranty expiration] FROM Query.ObjectCsy

SQL queries for the User permissions changes audit

Protocol for setting the permissions in the objects tree for the last month.
SELECT * FROM Query.NodeRightLog WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTCDATE())) AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE()))
Protocol for setting the membership in the groups for the last month.
SELECT * FROM Query.RoleMembershipLog WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTCDATE())) AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE()))
Protocol for user logins for the last month.
SELECT * FROM Query.UserLogonLog WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTCDATE())) AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE()))

 

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