• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

Counting the number of individual objects in the client_schedules objects field

rdemaat

ADSM.ORG Member
#1
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
 

rdemaat

ADSM.ORG Member
#3
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!
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 17 19.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 53 60.9%
  • Let's be formal and just say Spectrum Protect

    Votes: 10 11.5%
  • Other (please comement)

    Votes: 7 8.0%

Forum statistics

Threads
31,471
Messages
134,133
Members
21,569
Latest member
srinathkodela
Top