Volhistory Report Displaying Only Details for Most Recent Date

[jayp2200]

So my goal is to create a new custom report which will gather details from volhistory for tape volumes and only display the entries with the most recent date. I got as far as listing the details for all tape volumes but can't figure out how to narrow it down to display just the most recent date for each one. Here is my current select statement:

select volume_name as "Volume Name", Date(date_time) as Date, type as "Status" from volhistory where devclass='TSMLIB1_LTO' group by volume_name,date_time,type

Of course this select statement lists every entry for each volume so what else would I need to add to narrow it down?

 

[marclant]

Try using:

select volume_name as "Volume Name", max(Date(date_time)) as Date, type as "Status" from volhistory where devclass='TSMLIB1_LTO' group by volume_name,date_time,type

I did not test, not sure if it will work, but max() should give you the higest/most recent data

 

[jayp2200]

OK I tried that but no joy. I mean, that would seem to make perfect sense, but it still just displays all of the entries for each tape for whatever reason.

FWIW, I'd like to add that I am trying to run this using the TSM Management Console version 5.5.5.0 for reporting. Running that same select statement within the TSM Command Line Administrative Interface (version 6.2.3.0) yields the same results though. Our Windows based TSM server is at version 6.3.5.0.

 

[marclant]

Just this part works:

select volume_name,type,max(date_time) from volhistory group by volume_name,type order by volume_name,type

You can try building on that.

 

[jayp2200]

OK well that helped a little so we're making progress. However, with that statement, it still gives me two or three entries for each tape.

It looks like what it's doing it giving me the most recent entry for each type. So for example:

Volume Name: T00122L5 Date: 2015-10-02 Type: STGDELETE
Volume Name: T00122L5 Date: 2015-10-03 Type: STGNEW

 

[marclant]

Oh, that would make sense, volume+type together makes each unique.

Pretty much reached the maximum I can accomplish with SQL.

I wonder if you need a nested query. Because you essentially want to things. The last date used for each volume, and what it was used for.

 

[jayp2200]

OK so here's what I came up with so far:

select -
                vh.volume_name as "Volume Name",-
                vh.type as Status,-
                max(vh.date_time) as Date, -
                dr.location as Location -
from volhistory vh, drmedia dr -
where vh.devclass='TSMLIB1_LTO' and vh.volume_name=dr.volume_name -
group by vh.volume_name,vh.type,dr.location -
order by vh.volume_name,vh.type

It still gives me more than 1 entry for each tape, but I think it's at least more manageable. Unless anyone else has any further suggestions, I think I'll stick with this statement for now. With this, my report goes from 40+ pages down to 4.

Thanks for all your help!