The following series of articles are intended to give a crash course in SQL writing using techniques and queries specific to the Symantec Client and Asset Management Suites. This part will focus on simple steps for cleaner, more efficient base queries.
NEVER ALLOW GENERAL USERS TO HAVE ACCESS TO RAW SQL WITHOUT TAKING STEPS TO ENSURE THAT ONLY READ ACCESS IS AVAILABLE TO THE USER. TRUNCATE AND DROP TABLE ARE NOT YOUR FRIENDS IF USED INCORRECTLY.
SELECT: Used to return data into a results table. For most queries SELECT will be followed by [table].[column], in order to return data.
NOTE the “,” between each table/column combination. When a column has a space in the name you MUST bracket the value.
FROM: Defines the Table(s) that will provide data to the results
AS: Use to provide an alias for a column or table. Providing an alias for a table is very helpful to create a short name. This avoids the need to type “vComputer” each time you need to reference the table.
When providing an ALIAS for a results column you may use either brackets or quotes.
ORDER BY: Order the query by one or more column names in a DESC or ASC order.
Example:
SELECT vc.Name AS [Computer Name]
,vc.[OS Name] AS "Operating System"
,vc.[IP Address]
,vc.[Server]
FROM vComputer AS vc
ORDER BY vc.Name DESC
WHERE: Filters the results from the Query based on specified criteria. We begin with a WHERE statement, then a [table].[column] followed by criteria.
In the example below we are looking for any computer with a name beginning with “SYM” and ending with anything (as signified by the “%”). Next, we want to return computers that are being managed by the server “clu003.Enterprise.NCC1701”. Notice the AND between the two statements, we could have also used OR
Example:
SELECT vc.Name AS [Computer Name]
,vc.[OS Name] AS "Operating System"
,vc.[IP Address]
,vc.[Server]
FROM vComputer AS vc
WHERE vc.Name LIKE 'SYM%' AND vc.Server = 'clu003.Enterprise.NCC1701'
JOIN: This allows us to combine more than one table into a new results table based on a common relationship. Please review this link for more information and diagrams for the JOIN relationship: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
The most common that tend to be used are the following:
o JOIN
o LEFT JOIN
o RIGHT JOIN
o FULL JOIN
With Symantec Endpoint queries there is (most of the time) a consistent way of joining two tables. The unique identifier will be column “Guid” or “_ResourceGuid”. So the join will usually looking something like
FROM Table1 AS tb1
JOIN Table2 AS tb2
ON tb1.GUID = tb2.GUID
Example:
SELECT vc.Name AS [Computer Name]
,vc.[OS Name] AS "Operating System"
,vc.[IP Address]
,vc.[Server]
,ident.[OS Version]
,ident.[OS Revision]
FROM vComputer AS vc
JOIN Inv_AeX_AC_Identification AS ident
ON vc.Guid = ident._ResourceGuid
Security Scoping: By default when creating a new SQL report a block of SQL is generated that allows you to leverage the security scoping ability of the console. By default it looks like this:
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
[vri2_Resource].[Guid] AS [_ItemGuid],
[vri2_Resource].[Name]
FROM
[vRM_Resource_Item] AS [vri2_Resource]
WHERE
(
([vri2_Resource].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
)
Now, let’s break down what this is doing:
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
--DECLARE sets the Variable to the security guid(s) of the users running the report
SELECT
[vri2_Resource].[Guid] AS [_ItemGuid],
[vri2_Resource].[Name]
--SELECT includes a new line, the GUID as [_ItemGuid]. This allows the Right Click and Double Click Actions to function properly, but the underscore hides the value in the final report when viewed in the console.
FROM
[vRM_Resource_Item] AS [vri2_Resource]
--FROM in this standard, but know that for easier reporting, plan on the FIRST table used is the same one you’ll be using for security.
WHERE
(
([vri2_Resource].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
)
--WHERE referenced the Scope Collections using the variable from the DECLARE and only shows resources we are allowed to read.
Example: Now let’s apply this to the existing example.
DECLARE: just be copied and pasted into the query.
SELECT: We add the _ItemGuid referencing the vComputer View
WHERE: This is simply edited to include “vc.Guid” instead of “vri2_Resource.Guid”
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
vc.[GUID] AS [_ItemGuid]
,vc.Name AS [Computer Name]
,vc.[OS Name] AS "Operating System"
,vc.[IP Address]
,vc.[Server]
,ident.[OS Version]
,ident.[OS Revision]
FROM vComputer AS vc
JOIN Inv_AeX_AC_Identification AS ident
ON vc.Guid = ident._ResourceGuid
WHERE
(
(vc.Guid IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
)