Large Number of Reports with missing Events

[datavalue]

The NT EventVwr reports that we have a number of reports with missing events. I am after a CSP script to list out the reports with missing events.

I know how to list all the reports in the system using CI_INFOOBJECTS and I know how to list all the events. But I don't know how to identify which recurring reports have event ids which are not assigned to a valid File Event.

I have no idea why we have the missing events in the first place either. It would seem CE is removing the Events but still leaving the recurring reports dependent on the event id.

Additionally one of our folders contains 35000 reports but we have set the instance limit to 20000. CE is removing only a few reports a minute. When we try to access the Report through manage objects we cant see anything other than the most recent 100 entries using CE, Internet Explorer times out when we try and access anything other than the first page.
How can we view the last page, so we can do a Select all on the page to delete old entries???

We are using CE8.5

Any help would be gratefully appreciated. Our CE server seems to be in a bit of a mess at the moment.

 

[datavalue]

I have identified the script to list recurring reports with events and highlight which events are missing :


' The InfoStore object used to query the APS.
Dim IStore

' The name of the folder that will store the report.
Dim FolderName

' The path and file name of the report.
Dim ReportName
'Create the InfoStore object.
set IStore = Session.Value("IStore")
on error resume next
if isnull (IStore) then
Response.Redirect("Start.csp")
else
Set oEventID = IStore.PluginManager.PluginInfo("CrystalEnterprise.Event")

Dim NewReportID
Dim FolderID
Response.Write("<h2>Recurring instances that are dependent on events</h2>")

Dim Result, lRow
Set Result = IStore.Query("SELECT * FROM ci_INFOOBJECTS WHERE SI_RECURRING=1 AND SI_PARENT_FOLDER = 37137")

Response.Write("<TABLE border=1 cellPadding=1 cellSpacing=1 width=""75%"">")

'********************************************************
' LOOP THROUGH THE INSTANCES TO CHECK IF THEY ARE
' DEPENDENT ON A SPECIFIC EVENT
'********************************************************
if Result.Count > 0 then
for lRow = 1 to Result.Count
' There may be more than one, but for this example just take the first.
Set oInfoObject = Result.Item(lRow)

Set cEvents = oInfoobject.SchedulingInfo.Dependencies

If cEvents.Count > 0 then
Response.Write("<TR>")
Response.Write(" <TD>"& Result.Item(lRow).Id & "</TD>")
Response.Write(" <TD>"& "[" & Result.Item(lRow).Title & "]" & "</TD>")
Response.Write(" <TD>"& "[" & Result.Item(lRow).ParentID & "]" & "</TD>")
Response.Write("</TR>")
end if

For j = 1 to cEvents.Count
oEventID = cEvents.Item(j)
Response.Write("<TR>")
Response.Write(" <TD>&nbsp;</TD>")
Response.Write(" <TD>"& oEventID & "</TD>")
set oEvent = Nothing
Set oEvent = IStore.query("SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=" & oEventID).Item(1)
Response.Write("<TD>" & oEvent.Properties.Item("SI_NAME") & "</TD>")
Response.Write("</TR>")
Next
next
end if

Response.Write("</TABLE>")

end if
end function

call Main ' Start the script.


I don't know why Crystal is deleting our events from the system. We definitely have had these events working, for example in February for one our reports as the report was produced. We have since February not had a requirement to run that report so it has not been a problem, its only when we look at the NT event log, that we see that this report has constantly thrown an error that the event is missing. In the meantime we're going to make sure no further events are deleted and correct the events which are missing.