AnsweredAssumed Answered

Can you use a 'not like' clause in queries?

Question asked by douglas.wolters on Jan 19, 2015
Latest reply on Jan 19, 2015 by douglas.wolters

I am trying to create a custom report that shows me the top 35 servers with the highest percentage of disk space used. To further complicate things, I do not want to see any drives that have P: in them because that is our standard paging file drive letter.

 

Any report guru's want to take a stab at what I am doing wrong?

 

Using a report from this site, I am able to get a drive report but when I try to either narrow down the results to the top 35 or try to excluded P: drives the query either does not run (trying the TOP 35) or runs but still includes the P: drives. Here is the query trying to get only the P drives (my add in blue):

 

select TOP 35

                distinct(vm_instances.display_name),

                vmvstor.Drive,

                vmvstor.VStorage_Capacity_in_MB,

                vmvstor.VStorage_Used_in_MB,

                vmvstor.VStorage_Used_Percent,

                vmvCPU.Num_of_VCPUs,

                vmvMem.VMem_Capacity_MB,

                vmvstor.Date

from

  (select

                uuid,

                commodity_key as Drive,

                capacity as VStorage_Capacity_in_MB,

                (avg_value*capacity) as VStorage_Used_in_MB,

                (avg_value*100) as VStorage_Used_Percent,

                snapshot_time as 'Date'

                from vm_stats_by_day

                where property_type='VStorage' and property_subtype='utilization' and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day)) and commodity_key not like '%P:\%'

                order by uuid, snapshot_time)

                as vmvstor

join

                (select

                uuid,

                max_value as 'Num_of_VCPUs',

                snapshot_time

                from vm_stats_by_day

                where property_type='NumVCPUs' and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))

                order by uuid, snapshot_time)

                as vmvCPU

  on vmvCPU.uuid =  vmvstor.uuid

join

                (select

                uuid,

                (capacity/1024) as 'VMem_Capacity_MB',

                snapshot_time

                from vm_stats_by_day

                where property_type='VMem' and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))

                order by uuid, snapshot_time)

                as vmvMem

  on vmvMem.uuid =  vmvstor.uuid

join

                (select

                uuid,

                display_name as 'display_name'

                from vm_instances

                order by display_name)

                as vm_instances

                on vmvstor.uuid = vm_instances.uuid

                order by vmvstor.VStorage_Used_Percent desc;

Outcomes