Mike

Selecting data from multiple tables -
As you learned in Part I, you can use the select statement to lookup information stored in tables or views in the database.

Sometimes the select statement needs to fully qualify the tablename and columname. Consider the following example:

Select Name from Inv_AeX_AC_Identification, Inv_AeX_OS_Add_Remove_Programs

Would return an error of "Ambiguos column name" because Name exists in both tables! For this to work correctly, we'll need to fully qualify which table we are talking about. Try this:

Select Inv_AeX_AC_Identification.Name, Inv_AeX_OS_Add_remove_Programs.Name
from Inv_AeX_AC_Identification, Inv_AeX_OS_Add_remove_programs

We've now told SQL exactly which tables we are talking about so the columname is no longer ambiguos, but didn't that seem a bit redundant to type each table name twice?

Aliasing Table Names
To prevent unnecessary typing, SQL will allow us to give a tablename an alias anywhere that you identify the tablename from within the Query. This is usually during the FROM or JOIN statement.

I could re-create the above query by doing the following:

Select id.Name, ad.Name
from Inv_AeX_AC_Identification id, Inv_AeX_OS_Add_remove_programs ad

The returned results are exactly the same as they would be above. If you look at the queries underneath your Altiris "canned" reports, they use this extensively with aliases like T0, T1, T2 etc. Understanding this can be very helpful in customizing your own queries based on the original.

Using Select As
The above example gives us a very messy report for two reasons. The first reason is redundant column names. Because both columns are named "Name" that is exactly what would be returned in the report. Fortunately SQL allows you to put in an Alias during the select statement. Consider the folowing example:

Select id.Name as 'Computer Name', ad.Name as 'Program Name'
from Inv_AeX_AC_Identification id, Inv_AeX_OS_Add_remove_programs ad

This would return the same results as before, but now our column names in the report are no longer the same and much easier to read. Note the use of single quotes. This is necessary if the string contains a space. It's a good habit to be in.

Joining Tables
The query examples that we've used thus far have been correct in syntax, but the results of the query have been absolutely worthless. Using the above mentioned examples, the data in the two columns would have no relationship whatsoever and be independant lists in all actuality. If you are going to pull data from multiple tables and display the data where the data has a relationship to the value in the other table, we'll need to find a shared value to JOIN ON.

Consider this display of column names:

Inv_AeX_AC_Identification:
_resourceguid, Name, Domain, Last Logon User, OS Name etc

Inv_AeX_OS_Add_Remove_Programs:
_resourceguid, Name, Uninstall Path, Hidden, etc...

As you can see, the common value between all table names in the Altiris Database that helps us to identify which "Resource" the data corresponds to is the "ResourceGuid". The number in this column is the same as the number that shows in your Altiris Agent properties.

Consider the following example:

Select id.Name as 'Computer Name', ad.Name as 'Program Name'
from Inv_AeX_AC_Identification id INNER JOIN Inv_AeX_OS_Add_remove_programs ad
ON id._ResourceGuid=ad._ResourceGuid

Notice that this example is exactly the same as what we did previousely except for the use of the words "INNER JOIN" instead of comma. We also had to provide the instructions of what it was that we would be basing our join on which in Altiris will always be the GUID (usually listed as _resourceguid in the tables, listed as GUID in several of the views)

This example would only show data in each column that corresponds the same value in the _resourceguid column. Essentially you are now selecting the Computer Name and Program Names reported by each Resource. Now this is starting to look like a report!

Combine that with a WHERE as we did in SQL Basics part II and we'll really be cooking:

Select id.Name as 'Computer Name', ad.Name as 'Program Name'
from Inv_AeX_AC_Identification id INNER JOIN Inv_AeX_OS_Add_remove_programs ad
ON id._ResourceGuid=ad._ResourceGuid
where ad.Name like '%office%'+'%2003'

Will return a list of all computers with Office 2003 components installed!

And that concludes Part 3. Feel free to add comments to this thread.

-----------------------------------------------
Mike Ainsworth

Nick

Good job Mike!

__________________
Scire potentia est (knowledge is power)

andykn

An easy way to build up some SQL syntax is to use SQL Views.

Run SQL Server Enterprise Manager, attach to your SQL Server with your test Altiris db on it and expand the Altiris database in the left hand pane. Still in the left hand pane, right-click on 'Views' and select 'New View'.

In the white SQL pane you can paste SQL select statements and then click on the red exclamation mark run icon on the toolbar at the top. If the syntax is OK and does not use SQL constructs not supported by the SQL View generator you will see the tables used displayed in the top, grey, diagram pane.

If you don't have any SQL to start with, right-click on the grey diagram pane, select 'Add table' and select the Tables and/or other Views you're interested in.

Once you've got the tables and views in the grey diagram pane you create the joins by using the mouse to drag a field from one table/view to the corresponding field in another table/view (most commonly _ResurceGuid). By default you get an Inner Join. Right-click on the join to change it - it explains what the options are.

You can then select which fields you wish to display by selecting each on in the diagram pane. You can put criteria for each in the grid pane (Like '%nc6000%').

You can right-click in the grid pane and select Group By. This shows the Group by column, which gives lots of options I don't understand, but also a COUNT option.

Select the red exclamation mark run icon and your SQL is magically prepared.

If you want to just modify an existing default Altiris built in report, just clone it, edit it and cut and paste the SQL into the view generator. You may have to replace the variables/parameters manually. Other non clonable/editable built in stuff can be used by right clicking and viewing XML. You can then add tables/views.

As a last resort, you can see the SQL that Altiris uses by running SQL profiler. Start a trace, select the console item you are interested in, wait for the console to refresh with it and then stop the trace. You'll have a fair few lines to step through in the trace but a bit of practice will speed this up. Find the key line, cut and paste the Select statement out of what's usually an EXEC and paste it into the SQL pane of a new view. You'll need to replace all the @ variables with their values from the end of the statement before running.

The SQL Server Query Analyser is more forgiving of variables and exec statements but less user friendly. And don't forget to select the correct database name from the drop down list at the top.

Nick

Or start your query with the USE <database> statement.

__________________
Scire potentia est (knowledge is power)