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
	n.txtName [Object name],
	wo.Prop_Name [Logical drive],
	round(wo.Prop_Size/1073741824,2) [Capacity (GB)],
	round(wo.FreeSpace/1073741824,2) [Total free space (GB)],
	round(wo.FreeSpace/wo.Prop_Size*100,2) [Free space (%)]
FROM tblNode n
	JOIN tblClass c on c.intClassId=n.lintClassId and c.bComputer=1
	JOIN vComputerDetectLast cdl on cdl.lintComputerNodeId=n.intNodeId and cdl.lintDetectKindId=1
	JOIN tblDetect d on d.intDetectId=cdl.lintDetectId
	JOIN tblWbemObject wo on wo.lintDetectId=cdl.lintDetectId and wo.__CLASS='Win32_LogicalDisk' and wo.DriveType=3
	JOIN tblWbemObject wobl on wobl.lintDetectId=cdl.lintDetectId and wobl.__CLASS='Win32_EncryptableVolume' and wo.DeviceID = wobl.DriveLetter
	LEFT JOIN vPersonNodeRight_Read AS r ON r.liNodeId=n.intNodeId AND r.liPersonId=@personId
	LEFT JOIN (
		SELECT TOP 1 1 IsAmAdmin
		FROM tRolePerson AS RP
			JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId AND R.RoleBehaviorId=8
		WHERE RP.liRolePersonPersonId=@personId
	) adm on adm.IsAmAdmin=1
	left join (
		SELECT TOP 1 1 IsAnyAmRole
		FROM tRolePerson AS RP
			JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId AND R.RoleBehaviorId IN (10,11,12,13,14,15)
		WHERE RP.liRolePersonPersonId = @personId
	) amr on amr.IsAnyAmRole=1
	left join (
		SELECT TOP 1 1 IsAnyLicReader FROM vLicRight_Read WHERE PersonId = @personId
	) lr on lr.IsAnyLicReader=1
	cross join tblSystemSetting ss
WHERE n.IsActive=1
	and (adm.IsAmAdmin=1
		or ((amr.IsAnyAmRole=1 or lr.IsAnyLicReader=1)
			and (ss.bolNode_Rights=0 or r.liNodeId is not null)
		)
	)

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.txtName [User],
    usr.txtPath AS [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.intNodeId [User Id],
        max(l.dteDateTime) [Date of last move (UTC)]
    FROM tblHistory as l
		INNER JOIN vPropertyKind_InventoryNumber PIN ON PIN.lintNodeId = l.lintNodeId AND ISNULL(PIN.txtValue,'')!=''
        INNER JOIN tblNodeParent np ON np.lintNodeId=l.lintNodeId and np.lintNodeId <> np.lintParentNodeId
		INNER JOIN tblNode usr ON usr.intNodeId = np.lintParentNodeId
		INNER JOIN NodeCust ncUsr ON ncUsr.NodeId = usr.intNodeId AND ncUsr.[User] IS NOT NULL
		INNER JOIN tblKind K ON K.intKindCode = 5
		INNER JOIN ClassKind CK ON CK.ClassId = usr.lintClassId AND CK.KindId = K.intKindId
    WHERE l.intFlags=768
    GROUP BY usr.intNodeId 
  ) moves 
INNER JOIN tblNode usr ON usr.intNodeId = moves.[User Id] 
INNER JOIN NodeCust ncUsr ON ncUsr.NodeId = usr.intNodeId AND ncUsr.[User] IS NOT NULL
LEFT JOIN (  
	select
        ISNULL(max(D.IssuedDate),{d'1900-01-01'}) [Last PP Date (UTC)],
		ND.lintNodeId
	FROM vDocument as D 
		inner join NodeDocument as ND on ND.AMDocumentId=D.id
	WHERE D.lintDocumentKindId = 2 and D.dteRemoved is null
    GROUP BY ND.lintNodeId
) pp ON pp.lintNodeId = moves.[User Id]
LEFT JOIN (
	SELECT TOP 1 1 Have
	FROM tRolePerson AS RP
		JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId
	WHERE RP.liRolePersonPersonId=@personId
		AND R.RoleBehaviorId in (8,10,11,12,13,14,15)
) HaveAnyAmRole ON HaveAnyAmRole.Have = 1
LEFT JOIN (
	SELECT TOP 1 1 IsAdmin
	FROM tRolePerson AS RP 
		JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId
	WHERE RP.liRolePersonPersonId=@personId AND R.RoleBehaviorId=8
) AmAdmin ON AmAdmin.IsAdmin = 1
WHERE moves.[Date of last move (UTC)]>DATEADD(DAY,1,pp.[Last PP Date (UTC)])
	and case 
			when AmAdmin.IsAdmin is not null then 1
			when HaveAnyAmRole.Have is not null
				and exists(select 1 from tblSystemSetting where bolNode_Rights=0) 
				then 1
			when HaveAnyAmRole.Have is not null
				and exists(select 1 from vPersonNodeRight_Read r where r.liPersonId=@personId and r.liNodeId=usr.intNodeId)
				then 1
			else 0
		end = 1

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.intNodeId NodeId, 
	nc.[Type],
	o.txtName [Computer],
	o.txtPath,
	wo.IsActivated, 
	wo.IsEnabled, 
	wo.IsOwned, 
	wo.Manufacturer, 
	wo.ManufacturerVersion, 
	wo.ManufacturerVersionFull20, 
	wo.ManufacturerVersionInfo, 
	wo.PhysicalPresenceVersionInfo, 
	wo.SpecVersion
FROM tblNode o
join tblClass c on c.intClassId=o.lintClassId
LEFT JOIN NodeCust nc ON o.intNodeId = nc.NodeId
LEFT JOIN vComputerDetectLast cdl ON cdl.lintComputerNodeId=o.intNodeId AND cdl.lintDetectKindId=1
LEFT JOIN tblWbemObject wo ON wo.lintDetectId=cdl.lintDetectId AND wo.__CLASS='Win32_Tpm'
LEFT JOIN (
	SELECT TOP 1 1 Have
	FROM tRolePerson AS RP
		JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId
	WHERE RP.liRolePersonPersonId=@personId
		AND R.RoleBehaviorId in (8,10,11,12,13,14,15)
) HaveAnyAmRole ON HaveAnyAmRole.Have = 1
LEFT JOIN (
	SELECT TOP 1 1 IsAdmin
	FROM tRolePerson AS RP 
		JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId
	WHERE RP.liRolePersonPersonId=@personId AND R.RoleBehaviorId=8
) AmAdmin ON AmAdmin.IsAdmin = 1
WHERE c.bComputer=1
	AND o.IsActive = 1
	and case 
			when AmAdmin.IsAdmin is not null then 1
			when HaveAnyAmRole.Have is not null
				and exists(select 1 from tblSystemSetting where bolNode_Rights=0) 
				then 1
			when HaveAnyAmRole.Have is not null
				and exists(select 1 from vPersonNodeRight_Read r where r.liPersonId=@personId and r.liNodeId=o.intNodeId)
				then 1
			else 0
		end = 1

Query examples​

Disk drives and free space​

Windows 11 compatibility​

Users with transferred assets without a transfer protocol​

Computers and their scanned TPMs​

Query examples

Disk drives and free space

Windows 11 compatibility

Users with transferred assets without a transfer protocol

Computers and their scanned TPMs