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

Post a Comment

Popular posts from this blog

How to search Active Directory group members using Windows 10 built-in tools

-
Image
Problem I need to check Active Directory group members but have limited access to install any software on a remote Windows machine running on Windows Virtual Desktop infrastucture. Solution To search for the group name, open  File Explorer | Network | Network | Search Active Directory and if you cannot see the members on the result column, you can run cmd.exe and use the following command to see the members of the Active Directory group: net group THE_AD_GROUP_NAME /domain
Read more

How to check and reload PAC (Proxy Auto Configuration) file on Windows

-
Problem I need to check PAC file content being used by Chrome and Internet Explorer on my Windows PC however company administrator has locked down the proxy settings on browser and Windows 10. Solution Run the following command via CMD.EXE on Windows reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /f AutoConfigUrl To force Chrome to reload PAC file, use the following URL and click Clear bad proxies followed by Re-apply settings chrome://net-internals/proxyservice.config#proxy For Internet Explorer, you may need to reboot Windows for the PAC file to reload via Active-Directory Group-Policy.
Read more