Command Shell Examples
Useful SQL Queries
All disk sizes (GB) - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

All disk sizes (GB)

All disk sizes (GB)

  • Comments 15
  • Likes

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

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • 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.

  • What database are you running this query against?

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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

  • 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?

  • @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

  • 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

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

  • 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

  • Hi Jonathan,

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

  • 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.