Skip Navigation LinksALVAO 10.1ALVAO 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 (define new 100 most used properties) in the ALVAO Admin – 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 of all the computers in active registry and their free space on the drive.

The following query finds a status of the free space on the drive for all the computer objects.
SELECT Computer.NodeName, Computer.[TotalHDDFreeSpaceGB] AS [Free space on the drive] FROM Query.Computer

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 Computer.[NodeName],Software.Product FROM Query.Computer LEFT JOIN Query.Software ON [Software].ComputerNodeId = Computer.NodeId 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 Computer.[NodeName], [NodeDiary].[Date], [NodeDiary].[Subject], [NodeDiary].[Text] FROM Query.Computer LEFT JOIN Query.NodeDiary ON NodeDiary.NodeId = Computer.NodeId WHERE NodeDiary.Type=0 AND Subject = N'Move'

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].NodeName AS [User NodeName], Node.NodeName AS [Computer Name] FROM [Query].[User] LEFT JOIN [Query].NodeParent ON [User].[NodeId] = NodeParent.ParentNodeId LEFT JOIN [Query].Node ON NodeParent.ChildNodeId = Node.NodeId WHERE Node.Class = N'Computer/desktop' OR Node.Class = 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 NodeName [Object], Class [Type], [Inventory number], [Room], [User], [Name in the network] FROM Query.Node 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 NodeName AS [Object], Class AS [Type], [Inventory number], [Room], [User], [Name in the network] FROM Query.Node 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 Computer.NodeName AS [Computer], Product AS [Application], Installed, LicenseInventoryNumber AS [Computer inventory number], [User].NodeName AS [User], LicenseInvoiceNumber AS [License document number], LicenceName AS [License name] FROM Query.Software LEFT JOIN Query.Computer ON Computer.NodeId = Software.ComputerNodeId LEFT JOIN Query.[User] ON [User].NodeId = Software.[User] WHERE Product = N'ALVAO Service Desk 8.1'

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 NodeName, [Name in the network], [User], [Purchase date], [Inventory number], [Serial number], [Warranty expiration], TotalHDDSizeGB AS [HDD] , CPUName AS [Processor], OS FROM Query.Computer

Overview of interventions by date

List of events saved in the Log since the parameters entered. Grouping by objects.
SELECT NodeName AS [Object], Class AS [Kind], Date AS [Date], Subject AS [Subject], Text AS [Text] FROM [Query].[NodeDiary] LEFT JOIN [Query].[Node] ON Node.NodeId = NodeDiary.NodeId

Object movements by date

List of move events saved in the Log. Grouping by date.
SELECT NodeName AS [Object], Class AS [Kind], Date AS [Date], Subject AS [Subject], Text AS [Text] FROM Query.NodeDiary LEFT JOIN Query.Node ON Node.NodeId = NodeDiary.NodeId WHERE NodeDiary.Subject = N'Move'

Devices by date of purchase

Objects list by purchase date.
SELECT NodeName AS [Device], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User] FROM Query.Node

Devices by warranty

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

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.