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>
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 12 years ago
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 12 years ago
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 12 years ago
That sounds like a bug. You should always be able to alias the column heading regardless of what new reports or classic. - dchristian 12 years ago
John - jverbosk 12 years ago