Query

On this page, enter the SQL query.

Caution:

A poorly defined SQL query can irreversibly damage the Alvao database. Always create and test SQL queries on a testing copy of the production database.

Options:

Command panel
- Save - saves changes.
- Execute - runs the SQL query and previews the results.
Query editor - enter the SQL query.

You can use the following variables in the SQL query:

@personId - ID of the user (tPerson.iPersonId) running the data query.
@localeId - LCID (tLocale.iLocaleId) of the preferred language of the user running the data query.
@customId - the value of the customId query string parameter. You can use this variable in customizations, e.g., IEntityTab. For instance, the URL of the data query may look like this: https://contoso.onalvao.com/Alvao/DataQuery/Result?id=1&customId=1.

On the page Data queries, the users can view all columns returned by the SQL query. If the column name ends with "TicketId", "NodeId", "SoftwareLicenseId", or "PersonId" (e,q. "MentorPersonId"), the values are displayed as links to the corresponding entity page. When an entire row is selected, the entity page for the first such column is displayed on the right.

Query examples

The query must contain exactly one SELECT statement, in which column names do not repeat and it does not include any sorting.

All computers

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

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

All computers 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], ObjectLogEnu.[Record kind]
FROM Query.ObjectEnu
LEFT JOIN Query.ObjectLogEnu ON ObjectLogEnu.[Object id] = ObjectEnu.[Object id]
WHERE ObjectEnu.[Object of Computer type] = 'Yes' AND ObjectLogEnu.[Title] = N'Move' AND ObjectLogEnu.[Record kind] = 'Object history'

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]

Windows 11 compatibility

Download the compatibility.sql script to check which computers in the registry are compatible with Windows 11.

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.[Name] [User],
    usr.[Path in tree],
    dbo.fnLocalTime(moves.[Date of last move (UTC)], 'Central Europe Standard Time') [Date last moved],
    dbo.fnLocalTime(pp.[Last PP Date (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.[Type]=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.[Last PP Date (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.

Computers and their detected TPMs

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

SELECT 
	o.[Object id] NodeId, o.[Type],
    	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'

Changes in RAM size on active computers in inventory

The query below generates a list objects for which the RAM Size (GB) property was changed during the previous calendar month.

select 
    L.dteLog as [Date],
    N.txtName as [Object Name],
    N.txtClass as [Type],
    N.txtPath as [Path in tree],
    L.txtLog as [Message],
    P.sPerson as [User]
from tblLog L 
join vNodeClass N on L.lintNodeId = N.intNodeId
join tblKind K on K.intKindId = L.KindId
join tPerson P on P.iPersonId = L.liLogPersonId
where N.IsHidden = 0 and N.IsDiscarded = 0 and N.bComputer = 1 
    and K.intKindCode = 147 and L.lintLogKindId = 4
    and (DATEPART(MONTH, L.dteLog) = (DATEPART(MONTH, DATEADD(MONTH, -1, GETUTCDATE()))))

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