How to track the history of user actions or the history of events for a system object

It is very convenient to have a report that contains all the actions in chronological order related to a certain object or subject.

This can be an order in the system, actions of a certain person, or events related to the activity of persons in a certain department. 

How to organize data collection and their output for analysis?

Decision: 

1. All data is stored in log tables for certain objects with links to the user, related objects (for example, an order). There can be many such tables. The point is to keep the fact of the action in a structured form with the necessary data relationships.

You do not need to try to save (with duplication) in the log of a specific person. This is inflexible, difficult to change and leads to an excess of information - the data in fact has to be duplicated for different types of logs.

Usually, a table of such a log will have the following fields - date, who created it, status, note.

Log tables are filled in in the saveItem of the form, when changing the entity statuses( as-entities), table operations, search, or any other places in the code.

2. Create a table, the input will be the code of the entity and its ItemId. It can be a person in the system, an order in the system, or something else. 

The table always has a single structure: 

  • id  - event ID (if a universal structure is used, then it is something like human_ivanov, order_123)
  • created - date, time of the event and how much time has passed since that moment 
  • type - the type is an icon with an indication of the color. The icon sets the type of event - for a person, it can be Entering time in the timelog, changing the status of a task, Entering and exiting, Creating a task, etc.
  • username - this is the user that this action is associated with. For example, for an order, it can be who created the order, who confirmed the order, etc.

3. As part of the procedure for building an order at the input, we have a temporary table with a single structure, which is then output according to standard processing. 

CREATE TABLE #actionLog ( id nvarchar(64) primary key,
    username nvarchar(128), title nvarchar(256), text nvarchar(max),
    created datetime, typeCode nvarchar(64), itemID int, color nvarchar(20),
    icon nvarchar(32), iconColor nvarchar(20) )
  • id - action id
  • username - related action
  • title, text - event description
  • created - when was the event
  • typeCode - the code of the event type (for example, newTicket)
  • itemID - ID of the related entity (for example, ticketID)
  • color - market the color of the name (for example, #f00)
  • icon - icon Font Awesome (for example, fa fa-bars)
  • iconColor - color of icon (#f00)

Thus, the main task is to fill this temporary table with data from different log tables, which is then output to the user. 

4. Table snippet: 

<a href="#" class="as-table-modal" data-code="actionLog" data-itemid="human_5017" data-big="1" title="Log by user" data-title="Log by user">
   <i class="fa fa-clock"></i>
</a>

human_5017 - it tells us that this will be a log for a person with ID=5017, this parameter must be processed in the procedure itself, and the user's rights to view this data are also checked. 

Full version of the procedure: 

CREATE PROCEDURE [dbo].[crud_actionLog_getItems]
	@filters CRUDFilterParameter READONLY,
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare @minDate datetime = dateadd(day, -7, getdate())
    declare @us nvarchar(128) = '', @type nvarchar(20), @itemID int

    -- filters...
	declare @filterTitle nvarchar(128)
	select @filterTitle = Value from @filters where [Key] = 'title'

    declare @filterDate nvarchar(128),@createdFrom date = NULL, @createdTo date = NULL
	select @filterDate = Value from @filters where [Key] = 'created'
	select @createdFrom = min(try_convert(date,value,104)) from split(@filterDate, '-')
	select @createdTo = max(try_convert(date,value,104)) from split(@filterDate, '-')


    declare @filterItemID nvarchar(128)
	select @filterItemID = Value from @filters where [Key] = 'itemID'

    set @type = dbo.str_splitPart(@filterItemID, '_',1)
    set @itemID = try_cast(dbo.str_splitPart(@filterItemID, '_',2) as int)

    IF OBJECT_ID('tempdb..#actionLog') IS NOT NULL begin
   		DROP TABLE #actionLog
	end
	CREATE TABLE #actionLog ( id nvarchar(64) primary key, username nvarchar(128), title nvarchar(256), text nvarchar(max),
                             created datetime, typeCode nvarchar(64), itemID int, color nvarchar(20),
                             icon nvarchar(32), iconColor nvarchar(20) )


    -- extracting data for the table
    if(@type='human') begin
    	select @us  = username from hr_humans where id = @itemID
    	-- createTicket
        insert into #actionLog
        select top 100 'createTicket_' + cast(id as nvarchar) id,
        	createdBy username,
        	'Создал задачу #' + cast(id as nvarchar) +  ' ' + name +
            	' (' + (select top 1 name from tt_projects where id = t.projectID)+ ')' title,
            name text,
            createdDate created, 'createTicket' typeCode, id itemID, '' color, 'fas fa-ticket-alt' icon, '' iconColor
        from tt_tickets t
        where createdDate > @minDate and createdBy = @us
		order by id desc

    end

	-- SELECT 1 - output result
    select * from (
        select top 1000
      		created hide_created,
            isnull([id], '') [id],
            isnull([username], '') [username],

            isnull([title], '') [title],
            iif(len(color)>0, color, '') marker_title,
            isnull([text], '') [desc_title],
            isnull(convert(nvarchar(10),[created], 104), '') + ''+convert(nvarchar(5), [created], 108) +'' [created],
            dbo.as_timeDelay(datediff(minute, created, getdate())) + ' назад' desc_created,
             iif(len(icon)>0, '0, ' style="color: '+iconColor+'" ', '' )+'>', '')  [type]
        from #actionLog
        where (@filterTitle is null or @filterTitle ='' or title like '%'+@filterTitle+'%'  or [text] like '%'+@filterTitle+'%')
            --and (@createdFrom is null or @createdTo is null  or (created  between @createdFrom and @createdTo))
        order by  created desc
    )t1
	order by  hide_created desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

	-- SELECT 2
	select count(*) from #actionLog

	-- SELECT 3
    select 1 Compact, '12px' FontSize, 'light' theme, 1 HideTitleCount

	/*Select  '' Title,
		'' ToolbarAdditional,
		'' GroupOperationsToolbar,
		'' EmptyText,
		'' FastCreateLinkText, '' FastCreateDialogHeader, '' FastCreateDialogPlaceholder,
		0 FastCreateSearch, 0 FastCreateTextarea,
		 0 HideTitleCount,
		 0 DisableCellTitle,
		 '10px' FontSize,
		 '{filterCode}' FilterMakeup,
		 1 InstantFilter,
		   */

	-- 4 SELECT Footer data or kanban/gantt data

	drop table #actionLog
END

Thus, it is possible to collect data from different disparate log tables and output them in the format of a single history for a specific system object.

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}
Note