Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We run various reports directly from the vSphere 4.1 database, to get a tactical overview of things like snapshots, reservations, etc.

The problem I have at the moment is that I can't find out the guest's datastore usage.

So the VM is from VPX_VM:

SELECT VM.ID, ENTITY.NAME
FROM VPX_VM VM
INNER JOIN VPX_ENTITY ENTITY ON (VM.ID=ENTITY.ID)

/*
ID | NAME
41 | Guest1
/*

I can get the guest usage from VPX_GUEST_DISK:

SELECT *
FROM VPX_GUEST_DISK

/*
VM_ID | PATH | CAPACITY | FREE_SPACE
   41 | C:\  |    30 Gb |      10 Gb
   41 | D:\  |    25 Gb |      20 Gb
   41 | F:\  |   100 Gb |      15 Gb
   41 | G:\  |   100 Gb |       4 Gb
   41 | H:\  |   100 Gb |      20 Gb
   41 | I:\  |   250 Gb |      10 Gb
   41 | J:\  |    50 Gb |       9 Gb
*/

And I can find out which datastores are being used from VPX_DS_ASSIGNMENT:

SELECT VM.ID, ENTITY.NAME, ASS.DS_ID, DS.NAME
FROM
    VPX_VM VM
    INNER JOIN VPX_ENTITY ENTITY ON (VM.ID=ENTITY.ID)
    LEFT JOIN VPX_DS_ASSIGNMENT ASS ON (ASS.ENTITY_ID=VM.ID)
    LEFT JOIN VPX_DATASTORE DS ON (ASS.DS_ID=DS.ID)

/*
ID | NAME   | DS_ID | NAME
41 | Guest1 |    15 | Datastore1
41 | Guest1 |    21 | Datastore2
41 | Guest1 |    50 | Datastore3
41 | Guest1 |   380 | Datastore4
41 | Guest1 |   382 | Datastore5
*/

Note that there are only 5 assignments here, because the guest's F: and G: disks are on the same datastore, and it's I: and J: disks are on the same (different) datastore.

But I cannot find out is how much of each datastore is being used. What I really need is the link that tells me which datastore each VPX_GUEST_DISK is on.

This is what I need as an end-result:

ID | NAME   | DS_ID | NAME       | ASSIGNED
41 | Guest1 |    15 | Datastore1 |    30 Gb
41 | Guest1 |    21 | Datastore2 |    25 Gb
41 | Guest1 |    50 | Datastore3 |    25 Gb
41 | Guest1 |   380 | Datastore4 |   100 Gb
41 | Guest1 |   382 | Datastore5 |   300 Gb

or even

ID | NAME   | DS_ID | NAME       | ASSIGNED
41 | Guest1 |    15 | Datastore1 |    30 Gb
41 | Guest1 |    21 | Datastore2 |    25 Gb
41 | Guest1 |    50 | Datastore3 |   100 Gb
41 | Guest1 |    50 | Datastore3 |   100 Gb
41 | Guest1 |   380 | Datastore4 |   100 Gb
41 | Guest1 |   382 | Datastore5 |   250 Gb
41 | Guest1 |   382 | Datastore5 |    50 Gb

(The sizes I've shown are in Gb for clarity, but in the database are actually in bytes)

share|improve this question
add comment

1 Answer 1

Try this one:

select 
    ds.ds_name, ds.ds_capacity_gb, ds.ds_free_gb,
    -- vm.name, 
    substring(vm.name, len(ds.storage_url) + 1, len(vm.name) - len(ds.storage_url)) file_name, 
    CONVERT(decimal(10, 0), CONVERT(decimal(15, 0), vm.file_size) / 1024 / 1024 / 1024) AS file_size_gb,
    vm.type
from vpx_vm_fle_file_info vm, 
    (select name as ds_name, storage_url, CONVERT(decimal(10, 0), CONVERT(decimal(15, 0), CAPACITY) / 1024 / 1024 / 1024) AS ds_capacity_gb, 
        CONVERT(decimal(10, 0), CONVERT(decimal(15, 0), FREE_SPACE) / 1024 / 1024 / 1024) AS ds_free_gb
    from vpx_datastore) ds
where vm_id = 7000
    -- filter by datastores
    and substring(vm.name, 1, len(ds.storage_url)) = ds.storage_url

-- Comment this for all VM files; this filters only files over 1 GB approx. and file_size > 1e9

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.