How to extract Infinity Intelledox Log using SQL statement


A few weeks ago I was researching for ways to extract Management Log | View Details on Infinity Intelledox Version 9 and Version 10 to analyse failed actions (CRM, BPOINT, REST) and finally found the following SQL statement that saved my day. Hopefully it will save yours too... Cheers...

SELECT datetime_start as 'Start Date', iu.Username as 'User', t.Name as 'Project', messages.value('(Messages/Message/@Description)[1]','varchar(max)') as Message
FROM dbo.template_log tl join dbo.Intelledox_User iu on tl.User_ID = iu.User_ID join dbo.Template_Group tg on tl.Template_Group_ID = tg.Template_Group_ID join dbo.Template t on tg.Template_Guid=t.Template_Guid
WHERE messages.value('(Messages/Message/@Description)[1]','varchar(max)') is not null

UNION

SELECT datetime_start as 'Start Date', iu.Username as 'User', t.Name as 'Project', messages.value('(Messages/Message/@Description)[2]','varchar(max)') as Message
FROM dbo.template_log tl join dbo.Intelledox_User iu on tl.User_ID = iu.User_ID join dbo.Template_Group tg on tl.Template_Group_ID = tg.Template_Group_ID join dbo.Template t on tg.Template_Guid=t.Template_Guid
WHERE messages.value('(Messages/Message/@Description)[2]','varchar(max)') is not null

UNION

SELECT datetime_start as 'Start Date', iu.Username as 'User', t.Name as 'Project', messages.value('(Messages/Message/@Description)[3]','varchar(max)') as Message
FROM dbo.template_log tl join dbo.Intelledox_User iu on tl.User_ID = iu.User_ID join dbo.Template_Group tg on tl.Template_Group_ID = tg.Template_Group_ID join dbo.Template t on tg.Template_Guid=t.Template_Guid
WHERE messages.value('(Messages/Message/@Description)[3]','varchar(max)') is not null

UNION

SELECT datetime_start as 'Start Date', iu.Username as 'User', t.Name as 'Project', messages.value('(Messages/Message/@Description)[4]','varchar(max)') as Message
FROM dbo.template_log tl join dbo.Intelledox_User iu on tl.User_ID = iu.User_ID join dbo.Template_Group tg on tl.Template_Group_ID = tg.Template_Group_ID join dbo.Template t on tg.Template_Guid=t.Template_Guid
WHERE messages.value('(Messages/Message/@Description)[4]','varchar(max)') is not null

UNION

SELECT datetime_start as 'Start Date', iu.Username as 'User', t.Name as 'Project', messages.value('(Messages/Message/@Description)[5]','varchar(max)') as Message
FROM dbo.template_log tl join dbo.Intelledox_User iu on tl.User_ID = iu.User_ID join dbo.Template_Group tg on tl.Template_Group_ID = tg.Template_Group_ID join dbo.Template t on tg.Template_Guid=t.Template_Guid
WHERE messages.value('(Messages/Message/@Description)[5]','varchar(max)') is not null

Comments

Popular posts from this blog

How to rename your tenancy hostname of SharePoint Online (sharepoint.com)

How to migrate SharePoint Online SPWeb with custom SPList form (PowerApps) and Flows