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