XIR2 scheduling instances viewer

system · September 8, 2010, 6:31pm

Have you had any luck getting the Macro to display the Folder Path? I saw some code in a later post but wasn’t able to get it working? Please let me know. Thanks in advance.

bjmmoffatt (BOB member since 2009-05-20)

system · October 12, 2010, 3:19pm

For those that have upgraded to BO XI R2 SP6 we found that the Instance Viewer would NOT work, but I found the solution.

In the MACRO code for the WHERE clause, remove the “SI_SCHEDULEINFO.” schema name from the SI_STARTTIME.

NEW
WHERE SI_INSTANCE = 1 AND
(SI_UPDATE_TS >=’" & start_date & “’)AND
(SI_STARTTIME >= '” & start_date & “’)AND
(SI_STARTTIME < '” & end_date & “’)”)

OLD
WHERE SI_INSTANCE = 1 AND
SI_UPDATE_TS >=’" & start_date & “’ AND
SI_SCHEDULEINFO.SI_STARTTIME >= '” & start_date & “’ and
SI_SCHEDULEINFO.SI_STARTTIME < '” & end_date & “’”)

Shifty (BOB member since 2005-09-29)

system · October 25, 2010, 2:17pm

Hello guys, I try to figure out the patch too make this third version work with XI 3.1. When I read post, you said go to thread page 6…Well, Could send the patch again ? Thanks in advance. For me, I used this tools to clean some heavy reports…

patrickfx (BOB member since 2007-10-17)

system · December 23, 2010, 3:20pm

Rachidb - you rock! Thank you!!!

Roxy21 (BOB member since 2008-10-15)

system · May 2, 2011, 10:54pm

It is not working with BOXI 3.1 .Can some one give me an update tool for BOXI 3.1?

alex555 (BOB member since 2005-06-14)

system · May 3, 2011, 11:44am

Yes.

Joe

joepeters (BOB member since 2002-08-29)

system · May 4, 2011, 8:38am

The dates don’t seem to handle properly. I’m putting 05/01/2011 00:00:00 for the start of this month and then 05/05/2011 00:00:00 for the end date (end of today).

The start date is being converted to 2011.04.01.23:00:00

and the end date is being converted to 2011.04.05.23:00:00

As the format time function is converting the date into the format of yyyy.mm.dd.hh:nn:ss this looks well out.

If I add a watch to the startdate and enddate variables and edit them manually once they are set it works ok. Does anyone else have this issue?

dopple (BOB member since 2009-06-19)

system · June 13, 2011, 1:03pm

We just upgraded to 3.1 SP3 FP3.5 and are seeing a similar issue. I’ve updated the libraries and made the other changes necessary to have it run in 3X; it does run and pull back some data but it is not complete.

Something strange is going on with the dates. My guess is that it’s related to the bug that was discovered at the time change this spring. We have not yet applied that fix so I can’t be certain.

This is what happened to the dates I used.

Input box

Start date = 6/9/2011 11:50:00 PM
End Date = 6/10/2011 12:49:00 PM

LocalTimeToUTC = 6/10/2011 4:50 a.m.

Resulting dates

Start_date = 2011.06.10.04:50:00
End_Date = 2011.06.10.17:49:00

Two things:

It appears to not be making the adjustment from GMT to my time zone (CDT), so it’s off by 5 hours. Even when I adjusted for that, though, to see our overnight runs, it only brought back parital results. It showed two reports just after midnight, then none from there to around 5:00 a.m., then showed reports after that. Unfortunately, most of my critical stuff runs between the two

I tried the Joe Peters 3X version and it does the same thing, presumably because it’s based on this one as far as date manipulation goes.

Has anyone applied that time zone fix and run this afterwards? Has something changed with the date format on the records in the repository? Is there anything else anyone has done to resolve this?

Thanks again for the macro - we’ve been using it for quite a while and are missing it now! Instance manager can give us the same information, of course, but not in nearly as tidy a package to review.

Mary

Juddy5 (BOB member since 2002-11-22)

system · June 13, 2011, 8:53pm

This tool is great. However, I am not a programmer and can only do minor tweaks here and there to code.

In one of the other tools someone built he was able to grab the folder location of the object. His code looks like this:
'------------------------------------------------------------------------
'Get the Path
'------------------------------------------------------------------------

ParentFolderID = rsrow.ParentID
Set tempObj = iStore.Query("SELECT SI_ID, SI_NAME, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_ID=" &amp; ParentFolderID)
ParentFolderID = tempObj.Item(1).ParentID
Path = "/" + tempObj.Item(1).Title + Path

Do While ParentFolderID <> 0 And ParentFolderID <> 4
    Set tempObj = iStore.Query("SELECT SI_PATH, SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID=" &amp; ParentFolderID)
    Path = "/" + tempObj.Item(1).Title + Path
    ParentFolderID = tempObj.Item(1).ParentID
Loop
Path = "Home" &amp; Path

Rng(RowNum, 6) = Path
Path = ""

How/where would I add this to the code in your xls tool?

giggles7840 (BOB member since 2007-07-13)

system · June 14, 2011, 12:54pm

Hi giggles7840,

There’s an easier way to get the path; this is the code I use in VBA:

Function getPath(in_IO As InfoObject)
    Dim oIO As InfoObject: Set oIO = in_IO
    Dim outString As String
    
    Do While oIO.ID <> 0 And oIO.ID <> 4
        If Not oIO.Instance Then outString = oIO.Title &amp; "/" &amp; outString
        Set oIO = oIO.Parent
    Loop
    getPath = Left(outString, Len(outString) - 1)
End Function

Add that function at end of Module1, then change

        .Cells(1 + i, j + 1).Value = oInfoObject.Title

to

        .Cells(1 + i, j + 1).Value = getPath(oInfoObject)

Joe

joepeters (BOB member since 2002-08-29)

system · June 14, 2011, 1:12pm

Hi Mary,

We’re on SP3.1 in XI3 and SP3.4 in XIr2. We applied the I-can’t-believe-this-is-still-a-problem DST fix this spring, and I am not seeing the same problem that you are.

There is an oddity in the way dates are managed. Date conditions in a CMS query are entered in UTC, but the result is displayed in local time (not sure if it’s user’s local time, or server, in my case they are the same). So a query:

select si_update_ts  from ci_infoobjects where si_update_ts between '2011.06.14.04:00:00' and '2011.06.14.08:00:00' order by si_update_ts

will show me results for midnight-4:00AM EDT. So… I’m not sure what’s causing the problem you’re having, although it is possible that it’s related to DST.

Your reports aren’t set to Clean Up Instance, are they? If so, that would explain why they’re not showing up, since the instance would have been purged.

Joe

joepeters (BOB member since 2002-08-29)

system · June 22, 2011, 8:45pm

awesome! This worked perfectly! Thank YOU!

giggles7840 (BOB member since 2007-07-13)

system · June 30, 2011, 9:04pm

I just wanted to say thanks to Rachidb for sharing this tool. With some tweaks it’s working out great!!

And a thank you to everyone who asked and answered questions. I was able to get both folder path codes to work. I modified one to pull just the parent folder so I could sort on that column. I also changed the code to pull only pending/recurring reports in the future. All thanks to everyone on this thread (couldn’t have done it without you because I’m a total noob with vbs).

BJSmith (BOB member since 2011-06-06)

system · November 3, 2011, 9:30pm

Ahlan Rachid,

this is a Helpful tool. Thanks for sharing it with us.

I have two questions concerning your tool:

1-how can I please get the really Message Error from the CI_InfoObjects, because the error Message what I get via your Tool doesnt corresponding the error what I get in the Instance Manager for certain Instance? I want to retrieve the Message SI_SUBST_STRINGS from the SI_STATUSINFO, but I dont know how to do that.

2-Have you please an Idea why I get the error The request is not for the current CMS, it is for when I want to refresh the results?

Thank you for your feedback in advance,

Hicham

naidoo (BOB member since 2006-12-16)

system · August 4, 2012, 9:10am

Hi rachidb,
This is an excellent tool! Very userful. Thanks for sharing.
I want to extract the report folder path of the instances. Like how we see in Instance Manager of XI3 Ver.
Could you please share an excel that extract that details as well?
Or Can you guide, how to modify the existing macro to extract the Infoview folder path info?

mariasundarraj (BOB member since 2007-03-30)

system · September 13, 2012, 3:03pm

Thanks rachidb

For sharing with us . This is very useful for me.

lax_lambor (BOB member since 2011-09-27)

system · September 17, 2012, 3:21pm

Iupdated the libraries for 12.0 XI 3.1sp3, ran fine and quickly. Nice resource

tkline (BOB member since 2009-10-08)

system · September 17, 2013, 10:29am

Hello Member
Thanks for the valuable script.
I need to run this script automaticay for every day.
How i can pass current date , without date prompting.
How i hard code userid, password & server name & authentication
without using log on form.

Regards

kksoft2002 (BOB member since 2009-12-10)

system · January 7, 2014, 2:19pm

Hi Members
When i try to get the size of the instance using the vb macro which is in this download area i get error on this line

.Cells(1 + i, j + 1).Value = oInfoObject.Files.Item(1).Size / 1024 & " KB"

may if there is no value for some instance it will through error.

if some one fix this please pso the code

Regards
Repos

kksoft2002 (BOB member since 2009-12-10)

system · January 8, 2014, 1:09pm

A very quick-and-dirty solution would be:

On Error Resume Next
.Cells(1 + i, j + 1).Value = oInfoObject.Files.Item(1).Size / 1024 &amp; " KB" 
On Error Goto 0

or

If hasFiles(oInfoObject) Then
    .Cells(1 + i, j + 1).Value = oInfoObject.Files.Item(1).Size / 1024 &amp; " KB" 
End If
....
Function hasFiles(oI as InfoObject) as Boolean
    hasFiles=false
    On Error Goto wtf
    Dim a as Integer : a = oI.Files.Count
    If a > 0 Then 
        hasFiles = true
        Exit Function
    End If
wtf:
End Function

(untested)

joepeters (BOB member since 2002-08-29)