CUC CLI SQL Command Help

[Maren Mahoney]

The following SQL Command will purge voicemails out of CUC for a single user's ('vmuser1' in the below) voicemail box between specified dates:

run cuc dbquery unitymbxdb1 update tbl_FolderItem set deleted = 1 where arrivaltime between '2023-01-01 06:00:00' and '2023-07-01 06:00:00' and folderobjectid = (select folderobjectid from tbl_folder where mailboxobjectid= (select mailboxobjectid from vw_mailbox where description='vmuser1') and folderinfoobjectid = (select folderinfoobjectid from tbl_folderinfo where name='Inbox'))

As I am not a SQL expert, so.... Can anyone help me with editing this command so that it will do the "set deleted = 1" within the arrival time but only if the current status is "Seen"?

So, essentially, voicemails that have been seen/listened-to that are older than a certain datetime will have their status changed to 'set deleted = 1'?

Thanks for your help!

Maren

[Carlo Poggiarelli · Accepted solution]

Hi Maren

Here you are

run cuc dbquery unitymbxdb1 update tbl_FolderItem set deleted = '1' where seen = '1' and arrivaltime between '2023-11-01 00:00:00' and '2023-11-02 23:00:00' and folderobjectid = (select folderobjectid from tbl_folder where mailboxobjectid= (select mailboxobjectid from vw_mailbox where description='vmuser1') and folderinfoobjectid = (select folderinfoobjectid from tbl_folderinfo where name='Inbox'))

HTH

Regards

Carlo

[Maren Mahoney]

@Carlo Poggiarelli  - Thank you SO MUCH! I can't wait to try it out. -- Maren

(And I'll come back and mark it "Solution" providing it works - which I have no doubt it will!)

[Carlo Poggiarelli]

Hi Maren,

You are most welcome!

To convince you more , here below you can find  a sequence that I've done on my lab.

Here you can  see that there is a seen message in today's time range

Here I marked as deleted the seen message.

...and last, the result.

Hope this is what you are looking for.

Cheers!

Carlo

[Maren Mahoney]

That does work exactly as described, thank you so much @Carlo Poggiarelli ! 

While I am on the subject, do you happen to know a SQL command that will purge deleted items out of the database? Essentially, rather than waiting for the Message Aging Policy to purge deleted items after 1 day (which is what is currently set in our system), to have the system purge deleted items when I run the command?

Maren

[Carlo Poggiarelli]

Hi Maren

Thanks for your feedback

You can try to run the "run cuc sysagent task Umss.MessageAgingTask" command

Thanks

Regards

Carlo

[Maren Mahoney]

That one will immediately run the regular Message Aging Task, but does not "Clean Deleted Items" which is what I'm looking for. I've looked for such a command for some time, but come up empty. Thanks for getting back to me so fast.

Maren

[Carlo Poggiarelli]

Ok let's apply this trick.

Make the seen message a little old

run cuc dbquery unitymbxdb1 update tbl_FolderItem set deleted = '1' ,arrivaltime ='2019-11-01 00:00:00',modificationtime='2019-11-01 00:00:00' where seen='1' and arrivaltime between '2019-11-01 00:00:00' and '2023-11-07 23:00:00' and folderobjectid = (select folderobjectid from tbl_folder where mailboxobjectid= (select mailboxobjectid from vw_mailbox where description='VMUSER1') and folderinfoobjectid = (select folderinfoobjectid from tbl_folderinfo where name='Inbox'))

than run run cuc sysagent task Umss.MessageAgingTask

That will do the trick

Please let me know

Cheers

Carlo

[Maren Mahoney]

ooooOOHHHHHOoooo! Making the modification date 'old'. That's sneaky and I like it. I doubt my customer would allow it, but I think I will give it a go in our development platform.

Thank you!

Maren

[Carlo Poggiarelli]

ahahah

I doubt your customer will complain with that cause you are going to delete his dated messages and that’s what he’s asking for 

Cheers

Carlo