Share via

All disk sizes (GB)

  • Article

/*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

  • Anonymous
    January 01, 2003
    Simir - default behavior of this monitor is to change state when the second threshold type is exceeded.

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    August 19, 2009
    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.

  • Anonymous
    October 16, 2009
    What database are you running this query against?

  • Anonymous
    February 10, 2010
    The comment has been removed

  • Anonymous
    February 10, 2010
    The comment has been removed

  • Anonymous
    February 11, 2010
    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.

  • Anonymous
    December 06, 2010
    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?

  • Anonymous
    November 10, 2011
    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

  • Anonymous
    April 29, 2013
    Hi Jonathan, Do you have a query to find out the disk space utilized?