Counting the number of individual objects in the client_schedules objects field

[rdemaat]

I have many VM guest backup schedules. The OBJECTS field for each is a long list of vm guest names (20, 30 or more) separated by commas. I would like a select statement that shows me (counts) the number of individual objects within the OBJECTS field. The example below has 25 vm guest names. I'd like to see SCHEDULE_NAME VM_DM4-6 #_of_objects 25 as the output of a select. Any ideas are greatly appreciated! Thanks!

NAHOLLAP846>
select schedule_name, objects from client_schedules where schedule_name = 'VM_DM4-6'

SCHEDULE_NAME OBJECTS
VM_DM4-6 nahollap518,nahollap716,nahollap726,nahollap727,nahollap728,nahollap731,nahollap732,nahollap733,nahollap734,nahollap735,nahollap736,nahollap737,nahollap739,nahollap740,nahollap742,nahollap745,nahollap746,nahollap747,nahollap749,nahollap750,nahollap751,nahollap754,nahollap755,nahollap756,nahollap816

 

[marclant]

You can't easily count that in a field.

You might have better luck querying the SUMMARY_EXTENDED table. Check the DP for VMWare Reporting Enhancements, Appendix A.

 

[rdemaat]

Hi marclant, that helps a bunch! Using the summary_extend table I'm able to get this:

NAHOLLAP846>
run objtot 3-1 5

SCHEDULE_NAME SUB_ENTITY START_TIME
VM_DM3-1 hollla189 2019-04-06, 17:35:26
VM_DM3-1 nahollap450 2019-04-06, 16:35:38
VM_DM3-1 nahollap477 2019-04-06, 16:40:07
VM_DM3-1 nahollap488 2019-04-06, 17:33:34
VM_DM3-1 nahollap491 2019-04-06, 17:27:05
VM_DM3-1 nahollap496 2019-04-06, 17:20:32
VM_DM3-1 nahollap543 2019-04-06, 16:09:45
VM_DM3-1 nahollap543 2019-04-06, 16:29:48
VM_DM3-1 nahollap545 2019-04-06, 17:23:34
VM_DM3-1 nahollap546 2019-04-06, 16:32:44
VM_DM3-1 nahollap708 2019-04-06, 16:43:41
VM_DM3-1 nahollap799 2019-04-06, 16:26:05
VM_DM3-1 nahollap799 2019-04-06, 16:05:32
VM_DM3-1 nahollap83 2019-04-06, 17:29:24
VM_DM3-1 nahollap878 2019-04-06, 16:08:11
VM_DM3-1 nahollap878 2019-04-06, 16:28:31

Unnamed[1]
16

What I haven't figured out yet is how to count the duplicate sub_entity 's just once. The total should be 13, not 16. I've tried the DISTINCT a few different ways to no avail. But this is very close to what I was after. Thank you!

Here's my code:

NAHOLLAP846>
q scr objtot f=l

Name Line Number Command
OBJTOT 1 select schedule_name, sub_entity, start_time from summary_extended where schedule_name like '%$1' and sub_entity not like '%Aggregate%' and start_time between current_timestamp - $2 days and current_timestamp - ($2-1) days order by sub_entity

10 select count(*) from summary_extended where schedule_name like '%$1' and sub_entity not like '%Aggregate%' and start_time between current_timestamp - $2 days and current_timestamp - ($2-1) days

Thanks again!

 

[marclant]

Try:
select count(distinct(sub_entity)) from summary_extended where ...

 

[rdemaat]

That's it! Thank you!