Skip to main content

Query

On this page, enter the SQL query.

warning

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:

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") or an URL link (with protocol), 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 clause with unique column names. The ORDER BY and WITH clauses are not allowed.

Disk drives and free space​

List all 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 scanned TPMs​

The query displays a list of computers and their scanned 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'