Getting VM guest datastore utilisation from SQL

Question

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)