Script to show occupancy on stgpool for inactive nodes

[redplumptomato]

Hi there,

We have this script (which I got from another site) which shows us node data grouped by stgpool. However, I'd like to know the occupancy of all nodes, grouped by stgpool, but, inactive only.

Here is what we use now:
SELECT node_name, filespace_name, stgpool_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NOCD1' GROUP BY node_name, filespace_name, stgpool_name ORDER BY filespace_name

How can I get the above to show nodes that have not accessed the server in 30 days or more?

 

[ChrisRees]

Hi

This should do what you want..

SELECT aa.node_name, bb.filespace_name, bb.stgpool_name, SUM(bb.logical_mb) AS "Total MB" FROM nodes aa, occupancy bb where aa.lastacc_time<=(current_timestamp - 30 days) and aa.node_name=bb.node_name group by aa.node_name, bb.filespace_name, bb.stgpool_name ORDER BY bb.filespace_name

Hope that helps

 

[redplumptomato]

Hi there,

We have this script (which I got from another site) which shows us node data grouped by stgpool. However, I'd like to know the occupancy of all nodes, grouped by stgpool, but, inactive only.

Here is what we use now:
SELECT node_name, filespace_name, stgpool_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NOCD1' GROUP BY node_name, filespace_name, stgpool_name ORDER BY filespace_name

How can I get the above to show nodes that have not accessed the server in 30 days or more?

FANTASTIC!!!

Exactly what I was looking for!