Failed instances for a particular month

[Former Member]

Hi Experts,

Can you help me with the query to list the no of failed instances through query builder for the month of March only?

I'm also trying to get the data from instance manager as I'm interested to analyse the error with which the reports failed, but it is only listing it for a specific user even though its not selected in any of the criteria. Not so sure why

Regards,
Diego

[former_member185603 · Accepted solution]

Try this.

Select SI_NAME, SI_OWNER, SI_AUTHOR,
SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and
SI_SCHEDULEINFO.SI_STARTTIME>=<Date>

[Former Member · Accepted solution]

Hi Jawahar,

Unfortunately, its not a valid query.

Select SI_NAME, SI_OWNER, SI_AUTHOR,

SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and

SI_SCHEDULEINFO.SI_STARTTIME>=<3/01/15>

3/01/15 : mm/dd/yy

Should there be a between clause in the query ?

Regards,

Diego

[former_member185603 · Accepted solution]

Starttime should be in this format.

yyyy.mm.dd.hr.mm.ss

'2015.03.01.00.00.00'

[Former Member · Accepted solution]

Thanks Jawahar.

I was able to retrieve the data from the query builder.

Can you confirm that the retrieved data from the above query is only for the month of march ? Is there any particular parameter that we could add in the query which could confirm it ?

Regards,
Diego

[former_member185603 · Accepted solution]

In the select statement, add si_starttime

[Former Member · Accepted solution]

Unfortunately, the si_starttime is showing the dates for the month of April.

Regards,
Diego

[former_member185603 · Accepted solution]

Start with Select *, so you will see all the results. You can check which one gives the actual date.

[Former Member · Accepted solution]

Hi Jawahar,

Thanks for responding .

The * statement was helpful and SI_STARTTIME under the scheduling info did show some dates for the month of March however is there anything that we could do to restrict the data only for the month of march by some "between" statement, because SI_SCHEDULEINFO.SI_STARTTIME>= is also providing the data for month of April which at the moment is not required.

I'm not so sure how to create a query with a between statement.

Regards,
Diego

[Former Member · Accepted solution]

Please advice

Regards,
Diego

[Former Member · Accepted solution]

Here you go...!! try this out...

Select SI_NAME, SI_OWNER, SI_AUTHOR,SI_STATUSINFO,SI_STARTTIME,SI_ENDTIME from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and SI_SCHEDULEINFO.SI_STARTTIME between '2015.03.01.00.00.00' and '2015.03.31.23.59.59'

[Former Member · Accepted solution]

Hi Vijay.

Thanks for your response.

The query was still displaying the results for the month of April.

Could this be a bug in Query Builder as I'm currently on SP3 FP 3.6?

Regards,
Deigo.

[Former Member · Accepted solution]

There cannot be any bugs in Query Builder, never has been noted. Its the query that has the fault. As per your need below query would suit better

Select SI_NAME, SI_SUBMITTER, SI_ERROR_MESSAGE from CI_INFOOBJECTS where SI_SCHEDULE_STATUS=3 and SI_ENDTIME between '2015.03.01.00.00.00' and '2015.03.31.23.59.59'

[Former Member · Accepted solution]

Hi Ravi,

I tried the above query and it fetched 31 results; however it displayed only a particular report (BOE-NEW) which is incorrect.

BOE-NEW is a report which is scheduled by one of the users on a daily recurrence.

Regards,
Diego.

[Former Member · Accepted solution]

Are you logging into Query Builder using default Administrator? A particular user would be able to view the objects on which it has View access in Query Builder, same as other applications

[Former Member · Accepted solution]

Hi Ravi,

I'm logged in with an User who is a part of administrator group.

With the below query, I'm able to receive the output but it also includes the failed instances occurred in the month of April. But I would like to restrict the data to a particular month March.

Select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STARTTIME, SI_ENDTIME

SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and

SI_SCHEDULEINFO.SI_STARTTIME>='2015.03.01.00.00.00'

Regards,
Diego

[Former Member · Accepted solution]

Select SI_NAME, SI_SUBMITTER, SI_STARTTIME, SI_ERROR_MESSAGE,

SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and

SI_ENDTIME between '2015.03.01.00.00.00' and '2015.03.31.23.59.59'

What about above query?

[Former Member · Accepted solution]

Same Result Ravi.

Only failed instances of BOE-NEW report is being displayed.

Regards,
Diego

[Former Member · Accepted solution]

Thats pretty weird since we are not even specifying any object name in where clause, can you use * in select statement and check how many results are you getting?

Also check below query, its without between clause

Select SI_NAME, SI_SUBMITTER, SI_ERROR_MESSAGE from CI_INFOOBJECTS where SI_SCHEDULE_STATUS=3 and SI_ENDTIME > '2015/03/01 00:00:00' and SI_ENDTIME < '2015/03/31 23:59:59'