/build/static/layout/Breadcrumb_cap_w.png

K1000 Report to List Free Hard Drive Space on Replication Share Machines' Targeted Drives

I made this report to make it easier to track the free space on the drives of replacation share machines that are used for replication share data.  Results are sorted so by free disk space with the lowest at the top to save time reviewing.

Just replace SERVER#/WINXP# and the corresponding drive letter you want to monitor in the code and you should be set. 

I have this set up in the Schedule Reports section so it gets emailed to me every morning for review.

Hopefully this can be useful for someone. 

Also, if anyone else has a better way of doing this, please let me know.  I can report on a label I have for my replication machines, but this pulls all of the drives and I only want/need to list the drive used for replication share data (for each machine).  Thus this approach.

John

______________________________________

*Title*
Replication Shares Drive Space

*Report Category*
Replication Shares (Custom)

*Description*
Lists available HDD space on HDD used for storing KBOX Replication Share data (patches, scripts, MIs, etc).  
Monitor daily to ensure HDD doesn't fill up as this would prevent updates from working.

*SQL Select Statement*
SELECT MACHINE.NAME AS SYSTEM_NAME,MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME AS DISK_NAME
FROM MACHINE_DISKS  
JOIN MACHINE ON (MACHINE.ID = MACHINE_DISKS.ID)
WHERE ((MACHINE.NAME = 'SERVER1') AND (MACHINE_DISKS.NAME like '%Drive T:%'))
OR ((MACHINE.NAME = 'SERVER2') AND (MACHINE_DISKS.NAME like '%Drive W:%'))
OR ((MACHINE.NAME = 'SERVER3') AND (MACHINE_DISKS.NAME like '%Drive F:%'))
OR ((MACHINE.NAME = 'WINXP1') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP2') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP3') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'SERVER4') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'SERVER5') AND (MACHINE_DISKS.NAME like '%Drive F:%'))
OR ((MACHINE.NAME = 'SERVER6') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'WINXP4') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP5') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'SERVER7') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'SERVER8') AND (MACHINE_DISKS.NAME like '%Drive D:%'))
OR ((MACHINE.NAME = 'WINXP6') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP7') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
ORDER BY MACHINE_DISKS.DISK_FREE

*Break on Columns*
<blank>


Comments

  • Hey jverbosk cool idea!!!!

    I changed the SQL so you wouldn't have to hard code the machines. Let me know if it works.

    SELECT M. NAME AS SYSTEM_NAME,
    MD.DISK_FREE,
    MD. NAME
    FROM MACHINE M,
    REPLICATION_SHARE RS,
    MACHINE_DISKS MD
    WHERE M.ID = RS.MACHINE_ID
    AND M.ID = MD.ID
    AND MD. NAME LIKE Concat('%', SUBSTRING(RS.DESTINATION_DIR, 1, 2), '%')
    ORDER BY MD.DISK_FREE - dchristian 11 years ago
    • anyway you can update this? It doesn't work now. - sam240 7 years ago
  • Thanks dchristian, works like a charm! I figured there was a more efficient way to do it, but I'm not very savvy in the black arts of SQL coding.

    One difference I do notice is that the column headers are different:

    Mine - System Name, Disk Free (G), Disk Name
    Yours - Asset Id, Name

    I've been wondering this for a while (since I started playing with the new reports) - is there any way to specify the names of columns? I've tried using "variable AS 'Column Name'", which is the way things work in the "Classic Reports" but I've found that it either doesn't work at all or quasi-randomly applies the column names (i.e. to different columns than specified). Any guru tips here would be outstanding, as I do appreciate not having to manually edit the HTML code to adjust column width so much in the new reports.

    Thanks again!

    John - jverbosk 11 years ago
    • Hmm... Thats weird the variable as column should work.... - dchristian 11 years ago
  • That's what I thought as well, but for example, the report column names I get when running your code as is comes out like this:

    Asset Id Name

    I would expect the first column to be called SYSTEM_NAME and the others to appear as listed, but instead the Name column is shifted to the right and the third column is blank.

    I tried running the report with the SELECT line changed to this:

    SELECT M. NAME AS 'Machine',
    MD.DISK_FREE AS 'Free Space (GB)',
    MD. NAME AS 'Replication Drive Stats'

    But the report column headers are exactly the same. Not sure what is going on (particularly why the first column header is 'Asset Id', but this is similar to what I've run into with other non-classic reports I've run (i.e. column headers in the wrong column, blank column header on the far right column) and that's why I asked. Not sure if it's a bug or just something on my system (since I haven't see anyone else comment on it), but it is disappointing. Any suggestions would be greatly appreciated.

    Otherwise, I'm in good shape and could always just put this in the Classic Reports (which is what I've been doing anyways for reports I need to distribute).

    John - jverbosk 11 years ago
    • I think you broke it! :)

      That sounds like a bug. You should always be able to alias the column heading regardless of what new reports or classic. - dchristian 11 years ago
  • I should also have mentioned I have my KBOX at version 5.3.53053. Lots of versions are there.

    John - jverbosk 11 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ