Skip Navigation LinksALVAO 11.0ALVAO Asset ManagementSearching, reporting and data analysisSQL query and database scriptSQL query examples Skip Navigation Links.

Change Alvao version

Change language

SQL query examples

All computers in active records

The following query will find out the details of all computers.

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

All computers in active records with AcrobatReader installed

Here we join values from multiple views into one using the LEFT JOIN operator.

SELECT ObjectEnu.[Object name], Software.Product FROM Query.ObjectEnu
LEFT JOIN Query.Software ON [Software].ComputerNodeId = ObjectEnu.[Object id]
WHERE Software.Product = 'Adobe Acrobat Reader'

All computers and their move history

Query computer objects and some selected items of their history.

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

Disk drives and free space

Question about computer disk drives and free space status.

SELECT o.[Object name], d.[Logical drive], d.[Capacity (GB)], d.[Total free space (GB)], d.[Free space (%)]
FROM Query.DiskDriveEnu d
LEFT JOIN [Query].[ObjectEnu] o ON d.[Object id]=o.[Object id]

Users with transferred assets without a transfer protocol

This query displays users who have been transferred assets with a completed inventory number, but subsequently no handover protocol has been issued.

SELECT 
  usr.[Asset name] [User],
  usr.[Path in tree],
  dbo.fnLocalTime(moves.[Date last moved (UTC)],'Central Europe Standard Time') [Date last moved],
  dbo.fnLocalTime(pp.[Date of last PP (UTC)],'Central Europe Standard Time') [Date of last PP]
FROM (
    SELECT
      usr.[Object Id][User Id],
      max(l.[Record date (UTC)]) [Date of last move (UTC)]
    FROM Query.ObjectLogEnu l
      INNER JOIN Query.ObjectEnu o ON o.[Object id]=l.[Object id] AND ISNULL(o.[Inventory Number],'')!=''
      INNER JOIN Query.NodeParent np ON np.ChildNodeId=l.[Object id] 
      INNER JOIN Query.ObjectEnu usr ON usr.[Object id]=np.ParentNodeId AND usr.[Object kind]=N'User' 
    WHERE l.Title=N'Move' 
    GROUP BY usr.[Object id] 
  ) moves 
  INNER JOIN Query.ObjectEnu usr ON usr.[Object id]=moves.[User Id] 
  LEFT JOIN (
    SELECT
      l.[Object id],
      ISNULL(max(l.[Record Date (UTC)]),{d'1900-01-01'}) [Last PP Date (UTC)]
    FROM Query.ObjectLogEnu l
    WHERE l.[Record kind]=N'Document' AND l.Title=N'Transmission Log' 
    GROUP BY l.[Object id] 
  ) pp ON pp.[Object id]=moves.[User Id]
WHERE moves.[Date of last move (UTC)]>DATEADD(DAY,1,pp.[Date of last PP (UTC)])

Note: The transfer log only has a date in the log (and the time is always 00:00:00), so we need to add a day to it.

Audit user permission changes

Log of changes in permission settings in the object tree for the last month.

SELECT * FROM Query.NodeRightLog
WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTUTCDATE()))
  AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE()))

Log of changes in group membership settings for 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()))

Log of 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()))

Computers and their detected TPMs

The query displays a list of computers and their detected TPM chips.

SELECT 
        o.[Object id] NodeId, 
        o.[Object kind],
        o.[Object name] [Computer],
        o.[Path in tree],
        wo.IsActivated, 
        wo.IsEnabled, 
        wo.IsOwned, 
        wo.Manufacturer, 
        wo.ManufacturerVersion, 
        wo.ManufacturerVersionFull20, 
        wo.ManufacturerVersionInfo, 
        wo.PhysicalPresenceVersionInfo, 
        wo.SpecVersion
FROM Query.ObjectEnu o
        LEFT JOIN vComputerDetectLast cdl ON cdl.lintComputerNodeId=o.[Object id] AND cdl.lintDetectKindId=1
        LEFT JOIN tblWbemObject wo ON wo.lintDetectId=cdl.lintDetectId AND wo.__CLASS='Win32_Tpm'
WHERE o.[Object of Computer type]='Yes'

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 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 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.[ObjectId]
WHERE [User].[ObjectType] = N'User' AND (Node.[ObjectType] = N'Computer/Desktop' OR Node.[Object kind] = 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 kind],
 [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 kind],
 [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],
LicenseName 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],
LicenseName 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 in network], [User], [Purchase date], [Inventory number], [Serial number],
[Warranty expiration], [Total Hard Disk Capacity (GB)] AS [HDD], [Processor], [Operating System] 
FROM Query.ObjectEnu
WHERE [Object of Computer type] = 'Yes'

Hits by date

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

SELECT [Object Name], [Object kind], [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 kind], [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.