Video Screencast Help

Report Writing with Endpoint Management – Part 1

Created: 20 Sep 2012 | 2 comments
Language Translations
odami's picture
+1 1 Vote
Login to vote

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))))

   )

Comments 2 CommentsJump to latest comment

lotsill's picture

Great Article  smiley.  If only we can get some detailed information on the advanced features of the report builder in 7.x

Steve Petrasek

0
Login to vote
odami's picture

Personally I like to stick to striaght SQL for a lot of my queries, but that does give me an idea. At the end of this series of articles I'll see about going back and demonstraighting some of the same capabilities using the Report Builder. Thanks for the feedback!

0
Login to vote