- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-31-2023 09:09 AM
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
Solved! Go to Solution.
- Labels:
-
Other Collaboration Applications
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-02-2023 02:22 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-02-2023 02:22 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-03-2023 08:50 AM
@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!)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-03-2023 09:23 AM - edited 11-03-2023 09:31 AM
Hi Maren,
You are most welcome!
To convince you more
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 06:11 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 07:13 AM
Hi Maren
Thanks for your feedback
You can try to run the "run cuc sysagent task Umss.MessageAgingTask" command
Thanks
Regards
Carlo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 07:26 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 01:13 PM - edited 11-07-2023 01:13 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 01:24 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2023 01:37 PM
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
