topic Re: CUC CLI SQL Command Help in Collaboration Applications

CUC CLI SQL Command Help

Maren Mahoney — Tue, 31 Oct 2023 16:09:34 GMT

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

Re: CUC CLI SQL Command Help

Carlo Poggiarelli — Thu, 02 Nov 2023 21:22:45 GMT

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

Re: CUC CLI SQL Command Help

Maren Mahoney — Fri, 03 Nov 2023 15:50:33 GMT

@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!)

Re: CUC CLI SQL Command Help

Carlo Poggiarelli — Fri, 03 Nov 2023 16:31:44 GMT

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

Re: CUC CLI SQL Command Help

Maren Mahoney — Tue, 07 Nov 2023 14:11:24 GMT

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

Re: CUC CLI SQL Command Help

Carlo Poggiarelli — Tue, 07 Nov 2023 15:13:41 GMT

Hi Maren

Thanks for your feedback

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

Thanks

Regards

Carlo

Re: CUC CLI SQL Command Help

Maren Mahoney — Tue, 07 Nov 2023 15:26:57 GMT

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

Re: CUC CLI SQL Command Help

Carlo Poggiarelli — Tue, 07 Nov 2023 21:13:45 GMT

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

Re: CUC CLI SQL Command Help

Maren Mahoney — Tue, 07 Nov 2023 21:24:51 GMT

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

Re: CUC CLI SQL Command Help

Carlo Poggiarelli — Tue, 07 Nov 2023 21:37:07 GMT

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