BusinessObjects Board

Automatic XI 3 Inbox Purge Program Object

Author: Steve Rademacher
Author notes: Use at your own risk. Caution this script deletes records from your repository. This is not recoverable and is not supported by SAP. This code works for me but it is up to you to review and test the code yourself. The purpose of this code is to purge old inbox records for inboxes that have 1000 or more reports in them. There is no built-in feature to purge inbox documents. In addition, every report sent to an inbox is another copy of the same report. These inbox reports take up a lot of space in the FRS.

This code only purges objects with a date older than 100 days. A log file is appended to called c:\inboxdeletelog.txt. The code does not delete the log file so it will grow forever.
Platform: Windows
Version: 1.0
Description: The code should be saved as a .vbs file. It can be run from the Windows scheduler, from the Program Object scheduler in Business Objects or manually. To test the code, remove or comment out the inboxRecords.Delete(inboxRecord) line. To run the code, call it as such: cscript inboxdelete.vbs {CMSname} administrator {adminpassword} secEnterprise.

The SI_CHILDREN>999 line limits the code to finding inboxes that have more than 999 records in them. The DateAdd(“d”, -100, Date) code (3 times) limits to finding inbox reports that have a last updated date more than 100 days ago. The TOP 100 code limits to delete maximum 100 documents. Run time on my largest repository with the current settings is 30 seconds. To delete more than 100 records, it is best to run this .vbs once for each 100 records. If you change any of these settings, be aware that sdk code may error out at 9 minutes and may use a lot of CPU time for your CMS while it is running.


' ************************************************************************
' Deletes old Inbox records for any user with more than 999 items in
' their inbox.  Each time this is run, it will delete up to 100 documents.
' ************************************************************************

Dim APS
Dim UserID
Dim Password
Dim Aut
Dim oSessionManager
Dim oEnterpriseSession
Dim oSess
Dim OldSIName

OldSIName = " "

'For the logon, Authenticate as Following
APS = Wscript.Arguments(0)
UserID=Wscript.Arguments(1)
Password = Wscript.Arguments(2)
Aut = Wscript.Arguments(3)

'Create an Enterprise oSessionManager
Set oSessionManager = WScript.CreateObject("CrystalEnterprise.SessionMgr")
Set oSess = oSessionManager.Logon(UserID, Password, APS, Aut)


'Query the iStore and get up to 100 inbox items to delete
On Error Resume Next
'Create a new Variable for the login token
Set iStore = oSess.Service("", "InfoStore")

queryString = "select TOP 100 * from CI_INFOOBJECTS Where CHILDREN(""SI_NAME='Folder Hierarchy'"",""SI_KIND='Inbox' and SI_CHILDREN>999"") and SI_UPDATE_TS < '"
queryString = queryString &amp; Year(DateAdd("d", -100, Date)) &amp; "."
queryString = queryString &amp; Right("00" &amp; Month(DateAdd("d", -100, Date)), 2) &amp; "."
queryString = queryString &amp; Right("00" &amp; Day(DateAdd("d", -100, Date)), 2)
queryString = queryString &amp; "' order by SI_UPDATE_TS"

Set objFSO = CreateObject("scripting.filesystemobject")
Set logStream = objFSO.OpenTextFile("c:\inboxdeletelog.txt", 8, 1)
logStream.writeline "Server - DateTime: " &amp; APS &amp; " - " &amp; FormatDateTime(Now,0)
logStream.writeline "Query: " &amp; queryString

Set inboxRecords = iStore.Query(queryString)

for x = 1 to inboxRecords.Count
    Set inboxRecord = inboxRecords.Item(x)
    If inboxRecord.Properties.Item("SI_NAME").Value <> OldSIName Then
        logStream.writeline "SI_OWNER-SI_KIND-SI_UPDATE_TS-SI_NAME-SIZE: " &amp; inboxRecord.Properties.Item("SI_OWNER").Value &amp; _
        " - " &amp; inboxRecord.Properties.Item("SI_KIND").Value &amp; _
        " - " &amp; inboxRecord.Properties.Item("SI_UPDATE_TS").Value &amp; _
        " - " &amp; inboxRecord.Properties.Item("SI_NAME").Value &amp; _
        " - " &amp; inboxRecord.Properties.Item("SI_FILES").Properties.Item("SI_VALUE1").Value
        inboxRecords.Delete(inboxRecord)
    End If
    OldSIName = inboxRecord.Properties.Item("SI_NAME").Value
Next
IStore.Commit(inboxRecords)

oSessionManager.logoff
logStream.Close

Steve Rademacher :us: (BOB member since 2004-02-17)

Approved and moved to BOB’s Download section.

Thanks Steve for sharing! :+1:


Marek Chladny :slovakia: (BOB member since 2003-11-27)

I am looking forward for this solution but not sure how do i execute this code.

Can you please explain this a bit.

Regards,
Prashant 8)


prashant.saduwale (BOB member since 2010-03-19)

Please see the description area for instructions on how to run. It runs on Windows only.


Steve Rademacher :us: (BOB member since 2004-02-17)

Hi Steve,
Thanks for the code, I am trying to use this as a template for running a similar script, currently working running from Excel vba. However I’d like to move this to the server and schedule it.

I’m having a bit of a nightmare getting the VBa to run a in vbs though.

So I have tried you’re script on our servers and the script goes to ‘pending’ indefinitely. If I run it on the server using the cscript passing in the params I get the following:

Microsoft VBScript runtime error: ActiveX component can’t create object: ‘CrystalEnterprise.SessionMgr’

It would seem the script cannot find the BO libraries, although the full install including sdk’s is installed (and the excel macro can run from the same machine, although excel registers the extral reference for you I think).

Any idea what I’m doing wrong? Is there a declaration of the dlls needed for vbs?

thanks
James


jbayly2 :uk: (BOB member since 2005-10-24)

If you can’t run it in cscript, then it won’t work if you schedule it on the server. You should be able to run this from your desktop as a test. If you can’t run it then for some reason, you don’t have the proper libraries.

I built it with the XI 3.1 SP3 client software installed on my desktop. I then copied it to the sever and ran it without issue. Do you by any chance have 64-bit machines? That could cause an issue. I have not tested it on a 64-bit machine.


Steve Rademacher :us: (BOB member since 2004-02-17)

Ah, unfortunately we do run on 64bit, both my laptop and server. Doh!

Thought bo installed as 32bit on the server, but prob doesn’t affect the libaries etc.

Thanks for the reply, if you have any ideas on working round this i’m all ears?


jbayly2 :uk: (BOB member since 2005-10-24)

If you find a solution for running this on 64-bit please post here. I am planning on upgrading both my PC and server to 64-bit before the end of the year.


Steve Rademacher :us: (BOB member since 2004-02-17)

Cancel that last one, it works!! (on 64bit)

It was a problem with me passing in the params on the wscript. Removed and hardcoded and it works beautifully. Just need to work out what i did wrong on passing the params now.

Thanks again for the code


jbayly2 :uk: (BOB member since 2005-10-24)

Steve - just wanted to ask you…when this scripts scans for user inbox objects, does it go by last update date? If so, when a user opens an inbox document, does BO not automatically update the date to the today’s date? In that case how can we truly reach to older objects that were “Sent” to the user’s inbox prior to a certain date?

Thanks!


sdeshpan :us: (BOB member since 2005-06-28)

There is a created date SDK object that can pull the created date. Feel free to modify the code to use that field. When I wrote this I was most interested in those scheduled reports that dump into user’s inboxes. The reports just sit in the inboxes forever taking up more and more disk space and the users never delete them.

The script is using the last update date which I believe you are correct is changed to the current date/time when the report is opened. The user opening the report just pushes the purge date for that report out to later but it will still be caught and purged in the future. This is a good thing because by the user opening the report, they have shown that at least they are interested enough in that run of the report to look at it. I can then assume that they may want that report to stay around a little longer.

Good luck with it. If you make any major new versions of it, please post your changes here.


Steve Rademacher :us: (BOB member since 2004-02-17)

OK so you mean that if I too only want to address reports/instances that are dumped to user inboxes (opened or unopened by the user) via the Destination - Inbox option in the scheduled job, then what you have should be sufficient? I am not to concerned about the reports that are truly sent to the users by other users, but like you said…our Input FRS is running out of space due to all these historical instances lingering around in user inboxes.

Actually, it took us some time to realize that user inbox documents, even though are report instances generated as part of a scheduled job, actually reside in the Input FRS as opposed to the Output FRS…like in the case of regular output of scheduled reports to default destination.


sdeshpan :us: (BOB member since 2005-06-28)

You are correct. It will eventually get around to them. However the default is that it will not even look at inboxes with less than 1000 objects in them. I suggest you start high like I did and eventually lower it down to what your organization sees as a reasonable number.

I found many user inboxes with well over 1000 objects in them. I have a small group of users that had over 20 scheduled daily reports to distribute to all of their inboxes. They are very important reports so I cannot stop or change that process. 20 reports per day times 365 days = 7300 reports per year in EACH of their inboxes. :hb:

You are correct that inbox documents are not instances even if they were generated by a scheduled report. They do not go in the output FRS where you would normally expect them to be and are instead filling up the input FRS. To make matters worse, if a report is distributed to 3 user inboxes, you get 4 copies of the instance. One in the default enterprise location in the output FRS (unless it has been marked to be cleaned up after the run) and one separate copy in each user’s inbox in the input FRS.

I instruct my users that if they want to keep something long term that is in their inbox, they should copy it from their inbox to their Favorites folder.


Steve Rademacher :us: (BOB member since 2004-02-17)

What you said makes sense. Have you been able to put in any other best practices around Inbox/User Fav folder management? This can clear up everything from the past (hopefully) but what about controlling this behavior going forward? Also, do you do anything to limit instances in favorites?

One other thing…I am hoping this script deletes instances from the FRS as well, not just the CMS entries…right?


sdeshpan :us: (BOB member since 2005-06-28)

The SDK code tells the CMC to perform a delete on an object. That causes the CMC to remove the entry from the Repository as well as to delete the corresponding file from the FRS. It is the exact same call that occurs if you select that object in Infoview/CMC and delete it.

I allow unlimited documents, but limited instances, in the user favorite folders. Just be careful that you are periodically checking the number of documents in their favorites as it is easy to copy other other folders or documents into your favorites and I have found that some people have done that with large numbers of reports. Also keep in mind that user inboxes and favorites are automatically deleted by the system if their user ID is deleted.

Ensure you limit the number of instances at the top of the public folders and the top of the personal (favorites) folders. I limit instances by maximum number of instances and by maximum age. Instances are cleaned up as each scheduled report runs or by a built-in scheduled process that runs some time around 4 AM server time for all instances. To limit instances, I use the following on the top level of both the Public Folders and Personal Folders:

Actions - Limits in the CMC when at the top of the public folder and top of the personal folders. Check Delete excess instances when there are more than N instances of an object: and enter your instance limit. Add Everyone under Delete excess instances for the following users/groups and enter the same instance limit. Add Everyone under Delete instances after N days for the following users/groups and enter the maximum number of days to keep old instances. You can then add additional groups to have lower limits on those groups or lower limits on certain folders by using this same feature on those folders.


Steve Rademacher :us: (BOB member since 2004-02-17)

What about the Favorites folder filing up the FRS then? Those document also reside in the Input FRS, right? How do you deal with that deluge of reports/instances?


sdeshpan :us: (BOB member since 2005-06-28)

Favorites folder instances are in the output frs and their base reports are in the input frs.


Steve Rademacher :us: (BOB member since 2004-02-17)

Hi Steve and everyone …

  1. When we set up the “Actions -> Limits” options in CMC, does it take effect only from that point on?
    OR it will also delete the old/existing excess number of instances from the folders?

  2. How is it that the FRS space is gained by deleting the Failed instances from Instance Manager? Failed instances haven’t produced any o/p files at the first place, so it doesnt make sense to me. Please confirm.

Thanks,
Buddy


Buddy :india: (BOB member since 2006-08-04)

Buddy,

Actions -> Limits in the CMC takes effect the next time that scheduled recurrence is run or overnight if it is not run for 24 hours.

Failed instances do not take up space in the FRS. They are just a record in your repository database. I believe they do count as an instance though for deletion based on number of instances.


Steve Rademacher :us: (BOB member since 2004-02-17)

Thanks for the response Steve…
You said

… what happens overnight? is it some BO internal job (that comes with the setup itself) … OR is it something that needs to be scheduled by the administrator of the application?

Thanks for confirming about the Failed instances … In some other thread I had read someone saying that it will free up FRS space … but I wasn’t convinced.

Cheers,
Buddy


Buddy :india: (BOB member since 2006-08-04)