SQL Queries
General Tips
- 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."
- SQL queries allow to run only SELECT queries.
- 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].
- Don't use SELECT * FROM in the queries, always name specific columns, otherwise displaying of results may be slowed.
- 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
- Prior to creating custom queries we recommend to look at examples of views usage described below.
SQL Query SamplesBelow we provide some basic SQL queries to find data on objects.Display of all the computers in active registry and their free space on the drive.The following query finds a status of the free space on the drive for all the computer objects.SELECT Computer.NodeName, Computer.[TotalHDDFreeSpaceGB] AS [Free space on the drive] FROM Query.Computer
Display all computers in the active registry with AcrobatReader installedHere we join the values from multiple views into one using the LEFT JOIN operator.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 movingA query for computer objects and some selected items from their history.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 monitorsWe will demonstrate how to transform a query in the query language into SQL. The original query: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 This query will display the user objects, their desktop kind computers, and monitors. It also displays the total number of monitors under all users (no matter the computer type). When converting to SQL we will proceed in this way: first we will find all the User type objects that have not been deleted and their Computer type sub-objects.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'
Note: Unlike the original query in the query language, the final SQL query doesn't contain the total number of monitors. This value has to be found in an individual query.
Converting the standard EPQ queriesThe following SQL queries display the same data as the standard EPQ files.Assets by roomsObjects with the Inventory number property grouped by the Room property and ordered by name in the network and by class.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 usersObjects with the Inventory number property grouped by the User property.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 softwareInstalled software grouped by computers.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 systemsInstalled OS grouped by computers.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
ComputersComputers, their sub-objects, and properties.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 dateList of events saved in the Log since the parameters entered. Grouping by objects.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 dateList of move events saved in the Log. Grouping 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 purchaseObjects list by purchase date.SELECT NodeName AS [Device], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User] FROM Query.Node
Devices by warrantyObjects list by their warranty (property):SELECT NodeName AS [Device], CONVERT(datetime,[Purchase date]) AS [Date], [Room], [User],[Warranty expiration] FROM Query.Node
SQL queries for the User permissions changes auditProtocol for setting the permissions in the objects tree for the last month.SELECT * FROM Query.NodeRightLog WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTCDATE())) AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE())) Protocol for setting the membership in the groups for the last month.SELECT * FROM Query.RoleMembershipLog WHERE DatePart("m", [TimeStamp]) = DatePart("m", DateAdd("m", -1, GETUTCDATE())) AND DatePart("yy", [TimeStamp]) = DatePart("yy", DateAdd("m", -1, GETUTCDATE())) Protocol for user logins for the last month.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.
|