SQL Queries

General Tips

1. When creating the SQL queries we recommend to use the views created for easier access to data in the Asset Management. All the views designed for access to information from the AM have the database scheme "Query."
2. SQL queries allow to run only SELECT queries.
3. Always enter the custom column names in the View.[Column] format, for example Node.[Serial number]. You can rename the columns using the AS command, for example SELECT View.[Column] AS [Column View].
4. Don't use SELECT * FROM in the queries, always name specific columns, otherwise displaying of results may be slowed.
5. Views containing the object properties always contain 100 most used object properties. You can update those views (define new 100 most used properties) in the ALVAO Admin – Database maintenance – Refresh data for reports
6. Prior to creating custom queries we recommend to look at examples of views usage described below.

SQL Query Samples

Display of all the computers in active registry and their free space on the drive.

SELECT Computer.NodeName, Computer.[TotalHDDFreeSpaceGB] AS [Free space on the drive] FROM Query.Computer 

Display all computers in the active registry with AcrobatReader installed

SELECT Computer.[NodeName],Software.Product FROM Query.Computer LEFT JOIN Query.Software ON [Software].ComputerNodeId = Computer.NodeId WHERE Software.Product = 'Adobe Acrobat Reader'

Display all computer objects and the history of their moving

SELECT Computer.[NodeName], [NodeDiary].[Date], [NodeDiary].[Subject], [NodeDiary].[Text] FROM Query.Computer LEFT JOIN Query.NodeDiary ON NodeDiary.NodeId = Computer.NodeId WHERE NodeDiary.Type=0 AND Subject = N'Move'

Converting query language into SQL – displaying all the user objects, their desktop type computers, and the total number of their monitors

SELECT AS "User"=[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

SELECT [User].NodeName AS [User NodeName], Node.NodeName AS [Computer Name] FROM [Query].[User] LEFT JOIN [Query].NodeParent ON [User].[NodeId] = NodeParent.ParentNodeId LEFT JOIN [Query].Node ON NodeParent.ChildNodeId = Node.NodeId WHERE Node.Class = N'Computer/desktop' OR Node.Class = N'Monitor'

Converting the standard EPQ queries

Assets by rooms

SELECT MAX(NodeName) AS [Object], MAX(Class) AS [Kind], MAX([Inventory number]) AS [Inventory number], MAX([Room]) AS [Room], MAX([User]) AS [User], MAX([Name in the network]) AS [Name in the network] FROM Query.Node WHERE [Inventory number] IS NOT NULL GROUP BY [Room]

Assets by users

SELECT MAX(NodeName) AS [Object], MAX(Class) AS [Kind], MAX([Inventory number]) AS [Inventory number], MAX([Room]) AS [Room], MAX([User]) AS [User], MAX([Name in the network]) AS [Name in the network] FROM Query.Node WHERE [Inventory number] IS NOT NULL GROUP BY [User]

Installed software by software

SELECT Computer.NodeName AS [Computer], Product AS [Application], Installed, LicenseInventoryNumber AS [Computer inventory number], [User].NodeName AS [User], LicenseInvoiceNumber AS [License document number], LicenceName AS [License name] FROM Query.Software LEFT JOIN Query.Computer ON Computer.NodeId = Software.ComputerNodeId LEFT JOIN Query.[User] ON [User].NodeId = Software.[User] WHERE Product = N'ALVAO Service Desk 8.1'

Operating systems

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], LicenceName AS [Assigned license], LicenseActivationKey AS [OS – Serial number] FROM Query.Software LEFT JOIN Query.Computer ON Computer.NodeId = Software.ComputerNodeId WHERE Category = 1

Computers

SELECT NodeName, [Name in the network], [User], [Purchase date], [Inventory number], [Serial number], [Warranty expiration], TotalHDDSizeGB AS [HDD] , CPUName AS [Processor], OS FROM Query.Computer

Overview of interventions by date

SELECT NodeName AS [Object], Class AS [Kind], Date AS [Date], Subject AS [Subject], Text AS [Text] FROM [Query].[NodeDiary] LEFT JOIN [Query].[Node] ON Node.NodeId = NodeDiary.NodeId

Object movements by date

SELECT NodeName AS [Object], Class AS [Kind], Date AS [Date], Subject AS [Subject], Text AS [Text] FROM Query.NodeDiary LEFT JOIN Query.Node ON Node.NodeId = NodeDiary.NodeId WHERE NodeDiary.Subject = N'Move'

Devices by date of purchase

SELECT NodeName AS [Device], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User] FROM Query.Node

Devices by warranty

SELECT NodeName AS [Device], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User],[Warranty expiration] FROM Query.Node

SQL queries for the User permissions changes audit

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

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

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

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