Replacing WebReports query with SQL query

I have session relevance for pulling all our fixlets/task, however using web reports has proved unreliable and and the 2 min sync time is also problematic. IBM “unofficially” said we should look into using SQL instead.

There is one field that I am having trouble finding in SQL - DEFAULT ACTION.

Session Relevance (that i am trying to replicate):
(name of it, name of site of it, id of it, custom flag of it, type of it, (if (exists default action of it) then (content id of default action of it) else “NoDefaultAction”)) of bes fixlets whose (type of it as string = “Task” OR type of it as string = “Fixlet”)

SQL 2 query (missing the default action):
select distinct sitename=case when lod.Sitename IS NOT NULL then lod.Sitename else bf.Sitename end, bf.ID, bf.Name, isCustom=case when exists (select * from LOCAL_OBJECT_DEFS lod where lod.name=bf.Name) then ‘true’ else ‘false’ end
from BES_FIXLETS bf left outer join
LOCAL_OBJECT_DEFS lod on lod.name=bf.name and lod.id=bf.id;

select distinct sitename=case when lod.Sitename IS NOT NULL then lod.Sitename else bf.Sitename end, bf.ID, bf.Name, isCustom=case when exists (select * from LOCAL_OBJECT_DEFS lod where lod.name=bf.Name) then ‘true’ else ‘false’ end
from BES_TASKS bf left outer join
LOCAL_OBJECT_DEFS lod on lod.name=bf.name and lod.id=bf.id;

Does anyone know the table in BFenterprise where the default actions are listed for fixlets?

A good question to ask is why does BF not offer up a rest interface endpoint to get this data? Going after this data with SQL queries is all well and somewhat good but future releases of BF could make your query go south in a hurry.

I haven’t looked for it, but I bet it is in the BES_Objects and you’ll need to run fn_extractfields to get at it.

1 Like

This worked well. thanks for pointing me in the correct direction!

select distinct sitename=case when lod.Sitename IS NOT NULL then lod.Sitename else bf.Sitename end, bf.ID, bf.Name, isCustom=case when exists (select * from LOCAL_OBJECT_DEFS lod where lod.name=bf.Name) then ‘true’ else ‘false’ end, dbo.fn_Extractfield(‘Default Action’,0,bod.fields) as "DefaultAction"
from BES_FIXLETS bf left outer join
LOCAL_OBJECT_DEFS lod on lod.name=bf.name and lod.id=bf.id left outer join
BES_OBJECT_DEFS bod on bod.name=bf.name and bod.id=bf.id;

1 Like