RSA ACE Queries or how I spent my summer

Well...just got done spending way to much time writing what is probably a basic SQL query for what I know is an outdated RSA ACE server ( ver. 6.0). However, its to prepare for our eventual upgrade and help reign in the management of the system.

Problem: We have multiple business units that use SecurID authentication. These business units have various groups set-up with specific agent hosts assigned.

What I need is a list of all AgentHosts including their IP, Site, and Group. I can then send this to the POC for the site, who can verify the agent hosts and groups.

I won't bore you with my ramp-up time on OUTER and INNER joins, or the way I love to insert typos randomly, or how the ACE server only give useful 'syntax error' pop-up messages. Suffice it to say, something that should have only taken a few hours...blossomed into a couple day journey. Live and learn.

So, first the bookmarks:

The database schema's are listed here:
http://theether.net/download/RSA/SecurID/6.1/authmgr_admin_toolkit.pdf

Some info on syntax can be found:
http://theether.net/download/RSA/SecurID/6.1/authmgr_admin.pdf


Here is the first *working* version, there are no options..it just prints that information I needed for ALL AgentHosts:

SELECT SDClient.chName, SDClient.chNetAddress, SDSite.chName, SDGroup.chName FROM SDClient
LEFT JOIN SDEnabledGroup ON SDClient.iClientNum = SDEnabledGroup.iClientNum
LEFT OUTER JOIN SDGroup ON SDEnabledGroup.iGroupNum = SDGroup.iGroupNum
LEFT OUTER JOIN SDSite on SDClient.iSiteNum = SDSite.iSiteNum
ORDER BY SDSite.chName, SDGroup.chName, SDClient.chName

Easy enough, a few join statements...bam.