This Tutorial is intended to help the advanced TSM Administrator in developing complex SQL queries. We shall focus on real world examples, some of which may address problems you've already solved while others might be new.
Check out the Beginner's Tutorial for simple queries and SQL basics.
Before we start, we should take a brief look at how TSM handles user defined scripts. Since we don't want to enter our more complex queries over and over, we can store them inside of the TSM Database and run them whenever we want.
To define a Script in TSM, we use the command "define script" (check "h define script" in TSM for further details) or - and this is MUCH more convenient - we click through to "Object View -> Automation -> Server Command Scripts" on the Web interface. While on the Web, you can simply type away like you would with an editor, on the command line "define script" thing, you would have to enter line by line running "update script" each time.
Either way, you can define scripts of any complexity within TSM and reuse them as needed.
Once a script has been defined, you can execute it using the "run" command. Run allows you to enter parameters than can be used as variables in your script code. A variable is constructed using the dollar character "$", followed by the number of the parameter on the command line. We'll now construct a trivial example.
This script will print out all specified summary information from a certain activity and a certain entity. We start writing the select statement directly to see, how its working: