Open-AudIT

I've been asked to create a report that shows how much non-USB storage is used on each audited system.

The hard drive table only has a size column, there isn't a "space used" column. I planned to calculate the amount of space used from the partition table by subtracting partition_free_space from partition_size.

I created a view that only contains the non-USB hard drives. When I join that view to the partition table with hard_drive_uuid = partition_uuid and partition_disk_index = hard_drive_index, I get multiple results per hard drive for a lot of partitions. I don't think that's correct, and when I sum partition_free_space (and partition_size), I will get several times the numbers I should see. For example, partition 1 has a partition size of 1000. That partition is listed for three different hard drives. If I sum the partition size of the system, I'll get a value of 3000 rather than 1000.

I think the problem is that I cannot get the partitions properly correlated with the hard drives. Is there a way to do this?

Also, while I'm asking for help - is there an entity relationship diagram for the Open-AudIT database? I was unable to find one, and I think it would help me immensely.

I appreciate any advice you can give me about this query.