Add another criterion to the WHERE clause comparing NODES.DOMAIN_NAME to the desired name (... and nodes.domain_name='xxxxx' ...).
Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] IBM Tivoli Storage Manager support web page: http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "ADSM: Dist Stor Manager" <[email protected]> wrote on 11/27/2006 02:47:52 PM: > Thanks Andy. I tried that at one point but still I need to do this by > individual domains. > > > At 01:58 PM 11/27/2006 -0700, you wrote: > >I don't think the criteria for getting auditocc info for the node names is > >quite right. Try this: > > > >select nodes.node_name as "Client_Name", \ > > date(nodes.lastacc_time) as "Last Access", \ > > auditocc.total_mb as "Total Storage" \ > > from nodes, auditocc \ > > where nodes.node_name = auditocc.node_name \ > > order by 2 > > > >Also, make sure you run AUDIT LICENSE prior to the above in order to > >ensure most up-to-date numbers. > > > >Regards, > > > >Andy > > > >Andy Raibeck > >IBM Software Group > >Tivoli Storage Manager Client Development > >Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] > >Internet e-mail: [EMAIL PROTECTED] > > > >IBM Tivoli Storage Manager support web page: > >http://www.ibm. > com/software/sysmgmt/products/support/IBMTivoliStorageManager.html > > > >The only dumb question is the one that goes unasked. > >The command line is your friend. > >"Good enough" is the enemy of excellence. > > > >"ADSM: Dist Stor Manager" <[email protected]> wrote on 11/27/2006 > >01:08:42 PM: > > > > > I'm trying to generate a report for storage abusers, just three columns: > > > > > > Client Name Last Access Total Storage > > > > > > > > > This is the SQL statement That comes close: > > > > > > select distinct(nodes.node_name) as "Client > >Name",date(nodes.lastacc_time) > > > as "Last Access" ,auditocc.total_mb as "Total Storage" from > >nodes,auditocc > > > where auditocc.node_name in (select node_name from nodes where > > > domain_name='xxx ) order by 2 > > > > > > but column three is always wrong. > > > > > > What do I need to do to make the proper join here? > > > > > > > > > > > > Fred Johanson > > > ITSM Administrator > > > University of Chicago > > > 773-702-8464 > > Fred Johanson > ITSM Administrator > University of Chicago > 773-702-8464
