How to Identify Application groups which are not accessed or stopped

[rajesh]

Hi all,

I need to check how many application groups/folders are there which are not using by any user from certain period of time.

Can any one help me on this.

[Justin Derrick]

Quickest way to do this is to search the System Log 'Message Text' field for the Application Group name.  Set your range to be 90 days, and search for %ABC123% (where ABC123 is your AppGroup name) and hit search.  No results?  That AG is idle.  If there are results, you can look to see if data is being loaded, but not accessed.  If the data isn't being accessed, don't bother caching it -- move it directly to TSM at load time.

Clearly, this isn't programmatic, and I wouldn't want to do this for a thousand App Groups, but it works on a small scale.

-JD.

[rajesh]

Thank you JD for the response. But i have many application groups and is there any other way using any command that really helps me.

Once again thanks for your time in answering my quesiton.

[rick]

Programmatically this can be also searched on tables.. System log tables are stored as SL2, SL3, .... 

[Justin Derrick]

There's probably a way to extract the 'last load date' from arsload, arsag, or arsseg.  I don't have time at the moment to dig into this, but it's an interesting puzzle.  I'll poke around in one of my installs to see if I can find anything.

-JD.

[Greg Ira]

Just a thought on this.  A semi-quick dirty method to accomplish this might be to run your a query against the system log using your OnDemand Client.  You might need to break the query into more manageable pieces but after running the query right click on the result list, select all, right click again and copy.  Paste into a spreadsheet, sort and remove duplicate names and you would have a list of Application groups that were accessed during that timeline.  Cross reference that with your full list of application groups and you'll find which ones weren't accessed during that time period.

Not pretty but it works and not too much of an effort.

[rajesh]

Thank you JD and greg i will try the solutions provided by you.

[Justin Derrick]

Here are two SQL queries that will tell you the date of the last document in each Application Group.  This does not mean the Application Groups are idle (ie, nobody accessing them), just what the maximum date value is:

db2 "select substr(ag.name,1,25) as Application_Group,date(TIMESTAMP('1970-01-01-00.00.00.000000') + int(max(seg.stop_date)/86400) days ) as Max_Date_In_Database from arsag as ag,arsseg as seg where ag.agid=seg.agid and seg.stop_date > 36525 group by substr(ag.name,1,25) order by Max_Date_In Database"

db2 "select substr(ag.name,1,25) as Application_Group,date(TIMESTAMP('1970-01-01-00.00.00.000000') + int(max(seg.stop_date)) days ) as Max_Date_In_Database from arsag as ag,arsseg as seg where ag.agid=seg.agid and seg.stop_date < 36525 group by substr(ag.name,1,25) order by Max_Date_In Database"

Bonus points if you can tell me why two queries are necessary ("You suck at SQL!" won't get you any bonus points!).

This is really just a 'second best' answer to your original question, because parsing through the System Log to get exactly the data you're looking for is very difficult.

Good luck!

[rajesh]

Thanks JD for the solution you provided i will check and get back to you with confirmation.