cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
551
Views
4
Helpful
9
Replies

CUC CLI SQL Command Help

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

1 Accepted Solution

Accepted Solutions

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

 

Please rate all helpful posts "The more you help the more you learn"

View solution in original post

9 Replies 9

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

 

Please rate all helpful posts "The more you help the more you learn"

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

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

SeenMessage.png

Here I marked as deleted the seen message.

Mark Deleted.png

 

...and last, the result.

Query after deletion.png

 

Hope this is what you are looking for.

 

 

Cheers!

 

Carlo

Please rate all helpful posts "The more you help the more you learn"

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

Hi Maren

Thanks for your feedback

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

Thanks

Regards

 

Carlo

Please rate all helpful posts "The more you help the more you learn"

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

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

Please rate all helpful posts "The more you help the more you learn"

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

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

Please rate all helpful posts "The more you help the more you learn"
Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: