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:

• @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") 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'