All disk sizes (GB)


/*Get each logical disk size, for each agent computer, by OS version.
This helps in calculating the Logical Disk Free Space Monitor from my
earlier post.  You can copy results into Excel, sort by system and
non-system drives, and perform an average disk size formula.  Then
plug Min, Max and Avg sizes into my Logical Disk Free Space Calculator
to find your unique MB and % thresholds for your company’s unique
requirements.*/

SELECT     PrincipalName AS ‘Windows 2000’, DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS ‘Drive’, CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS ‘Size’
FROM         MTV_LogicalDisk
ORDER BY ‘Windows 2000’, ‘Drive’

SELECT     PrincipalName AS ‘Windows 2003’, DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS ‘Drive’, CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS ‘Size’
FROM         MTV_LogicalDisk_0
ORDER BY ‘Windows 2003’, ‘Drive’

SELECT     PrincipalName AS ‘Windows 2008’, DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS ‘Drive’, CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS ‘Size’
FROM         MTV_LogicalDisk_1
ORDER BY ‘Windows 2008’, ‘Drive’

 

Back to SQL queries main menu

Comments (15)

  1. Anonymous says:

    Simir – default behavior of this monitor is to change state when the second threshold type is exceeded.

  2. Anonymous says:

    Or should I say when both threshold types are exceeded…

    Have you seen this article yet?

    blogs.technet.com/…/logical-disk-free-space-monitor.aspx

  3. Anonymous says:

    Hi Tom,

    If you do not have any Windows 2000 Servers, then remove the LAST select statement and change the other column names accordingly.  This is why you are getting the error, because these tables are dynamically numbered when created.

    This was also explained in the comment above, "… Keep in mind, these are typed views, and assume that you have the Windows Server Operating System MP imported…".

    -Jonathan

  4. Anonymous says:

    I don't have one handy, but this should be relatively easy to get since the related data is collected as a performance counter out of box.

  5. Anonymous says:

    Hi Tom,

    It’s interesting that you’ve got MTV tables that are empty.  I wonder if you had deleted the Windows OS MP at some point and re-imported it?  Whatever the case, I’m glad you figured it out.  The query above is rudimentary, but returned what I needed at the time.  It get’s the job done if you’re willing to make some slight modifications if needed.

    By the way, I’ve got a Command Shell script that does basically the same thing, but the script never needs modification in any envinment.  That can be found here:

    http://blogs.technet.com/jonathanalmquist/archive/2010/01/29/return-discovered-inventory.aspx

    -Jonathan

  6. Anonymous says:

    Hi rdurbin,

    This queries the OperationsManager database.  Keep in mind, these are typed views, and assume that you have the Windows Server Operating System MP imported, as well as Windows Server 2000, 2003 and 2008 discovered logical disks.  If there are only W2K3 server disks in your environment, then there will only be one MTV_LogicalDisk view (i.e., no _0 or _1).

    It’s a quick and dirty query. 🙂

    -Jonathan

  7. Anonymous says:

    @DayDots – not easily done with the MTV tables in the query here.  I suggest looking at the sample report by Dan Savage here: blogs.technet.com/…/some-sample-reports.aspx

  8. David Ruchala says:

    How easy would this be to convert it to get the install physical ram?  I'm not a SQL DBA nor am I all that familiar with the structure of the Operations Manager Database.

  9. rdurbin says:

    What database are you running this query against?

  10. Tom Kasmir says:

    I am using the Logical Disk Size monitor overrides in a much better way than before. I had to do a lot of manual looking up of volume sizes on servers but would like to refine this process. To that end the above query gives me this error:

    Invalid column name ‘Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543’.

    Any idea why? I copied and pasted the exact wording and left off the first third of the query because we have no W2K servers. So that error shows up twice in the results window.

  11. Tom Kasmir says:

    I copied and pasted the query but get the following error:

    Invalid column name ‘Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543’.

    Not sure why it is taking the math part of the CONVERT formula and considering that to be a column name.

    – thanks

  12. Tom Kasmir says:

    Thanks JA. You put me on the right track. At first it was a no-go. But sfter looking at the column names in the database and plugging in various numbers for the MTV_LogicalDisk_x values in the query this is what finally worked in an environment with only Windows 2003 and 2008 servers:

    MTV_LogicalDisk    (Windows 2003)

    MTV_LogicalDisk_5  (Windows 2008)

    Trying MTV_LogicalDisk_2 for the 2nd statement partially worked; it allowed the query to complete and showed all W2K3 disks, but while there was a section for Windows 2008 disks it was empty. It was only using MTV_LogicalDisk_5 in that section that gave me all disks. Excellent query! Thanks.

  13. DayDots says:

    Jonathan,

    Is it possible to add a filter in this query? For instance, I have Groups set up for teams, ex. TeamX_Servers

    So on the query I would want to add another column for Team or Group?

    Does this make sense?

  14. SAMIR FARHAT says:

    Hi Jonathan,

    Thanks for this helpful post.

    I have a question please: For an alert to be raised, have the two thresholds MB and % be crossed in the same time ? Or if at least of one threshold is crossed, than an alert is raised ?

    Thanks in advance for your contribution

  15. Jesty says:

    Hi Jonathan,

    Do you have a query to find out the disk space utilized?