meeta
meeta
Offline
1
I can run the 'User Change history' report in SBM -->Reports -->System Report --> User Change history to see who created, modified, deleted and undeleted the user in SBM. Is there a report which tells me what's been modified? Is this information available in any log file?
Responses (5)
  • Accepted Answer

    Tuesday, October 24 2017, 09:59 AM - #Permalink
    0
    Any official statement on this yet ?
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, October 24 2017, 04:38 PM - #Permalink
    0
    This isn't an "official statement" (I am just a SBM user), but as far as I am aware there isn't any report that provides additional information - and I don't think there is a log file where this is recorded.

    The details in the report come from the TS_ADMINCHANGES table, and it doesn't store anything additional that isn't on the User Change History report.
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, October 25 2017, 11:39 AM - #Permalink
    0
    SBM does not track what changed about a User record, only that it did in fact change (TS_ADMINCHANGES). Currently, the only way I can think of to track the exact changes on a user would be to set up a table to write your audit data to, then create a trigger on the TS_USERS table that writes to this audit table when a record changes. If that audit table was made to be an aux table, you could then run reports on it (keep in mind that if your trigger needs to write new rows to an aux table, it would also need to update the TS_LASTIDS table).

    There may be an enhancement request from Central about tracking changes to users, which of course you could upvote. Auditing changes to reports was recently added to SBM.
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, October 25 2017, 11:47 AM - #Permalink
    0
    -- dup / deleted --
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, October 25 2017, 11:47 AM - #Permalink
    0
    Here's some SQL (MS SQL Server specific) that dumps admin changes for the last 8 days. As noted by Don, the actual "Previous Value / New Value" data is not logged.

    If you copy/paste from this, watch out for single & double quote characters being converted to UTF versions that cause syntax errors.



    -- All Admin changes for the last 7 days

    -- UserID if occasionally missing (0)


    Select
    admchg.TS_ID As [ID] ,
    -- admchg.TS_ACTION As [ACTION] ,

    -- admchg.TS_TIMESTAMP As [TIME STAMP] ,
    DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970') as [change timestamp] ,
    DateDiff(dd,DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970'),GetUtcDate()) As [Days Old],

    -- admchg.TS_USERID As [UserID] ,
    Cast(
    Case IsNull(admchg.TS_USERID,0)
    When 0 Then '<none>'
    Else (Select usr.ts_Name from ts_Users usr Where usr.ts_Id=admchg.TS_USERID)
    End as VarChar(32)
    ) as [User who performed action] ,

    Cast(
    Case IsNull(admchg.TS_ACTION,-1)
    When 0 Then 'SUBMIT'
    When 1 Then 'UPDATE'
    When 2 Then 'DELETE'
    When 3 Then 'ATTACHMENT_ADD'
    When 4 Then 'ATTACHMENT_UPDATE'
    When 5 Then 'ATTACHMENT_DELETE'
    When 7 Then 'VC_UPDATE'
    When 8 Then 'LINK_SUBTASK'
    When 9 Then 'LINK_PRINCIPAL'
    When 10 Then 'UNLINK_PRINCIPAL'
    When 11 Then 'UPDATE_SUBTASK'
    When 12 Then 'UPDATE_PRINCIPAL'
    When 13 Then 'MARK_USER_DELETED'
    When 14 Then 'UNDELETE_USER'
    When 15 Then 'DISABLE_USER'
    When 16 Then 'ENABLE_USER'
    When 17 Then 'IMPORT_NEW_RECORD'
    When 18 Then 'UPDATE_BY_IMPORT'
    When 19 Then 'OOO_DELEGATION'
    When 20 Then 'PAUSE'
    When 21 Then 'UNPAUSE'
    When 22 Then 'TIMECAPTURE_ADD'
    When 23 Then 'TIMECAPTURE_UPDATE'
    When 24 Then 'TIMECAPTURE_DELETE'
    When 25 Then 'ADDEDTOBACKLOG'
    When 26 Then 'REMOVEDFROMBACKLOG'
    When 27 Then 'CHANGEDPRIORITY'
    Else '<unknown>'
    End as VarChar(24)
    ) as [Action] ,

    -- admchg.TS_TABLEID As [TableId] ,
    Cast(
    Case IsNull(admchg.TS_TABLEID,0)
    When 0 Then '<none>'
    Else (Select tbl.ts_DbName from ts_Tables tbl Where tbl.ts_Id=admchg.TS_TABLEID)
    End as VarChar(28)
    ) as [Table Changed] ,

    admchg.TS_RECORDID As [Rec changed] ,

    Cast(admchg.TS_NAME as VarChar(32)) As [Name on record changed]
    From
    ts_AdminChanges admchg
    Where
    -- For the last XX days
    (DateDiff(dd,DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970'),GetUtcDate())) < 8
    -- (DateDiff(dd,DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970'),GetUtcDate())) < 200
    Order By
    admchg.TS_TIMESTAMP
    ;






    -- Tables that show up in ts_TableId

    -- ts_id ts_name ts_dbname
    -- ------ ---------------------------- ---------------------------------
    -- 3 Fields TS_FIELDS
    -- 4 Groups TS_GROUPS
    -- 6 Members TS_MEMBERS
    -- 7 Privileges TS_PRIVILEGES
    -- 8 Projects TS_PROJECTS
    -- 9 ProjectSelections TS_PROJECTSELECTIONS
    -- 10 Properties TS_PROPERTIES
    -- 12 Selections TS_SELECTIONS
    -- 17 Users TS_USERS
    -- 18 Tables TS_TABLES
    -- 20 Folders TS_FOLDERS
    -- 25 Notifications TS_NOTIFICATIONS
    -- 26 NotificationRules TS_NOTIFICATIONRULES
    -- 27 NotificationConditions TS_NOTIFICATIONCONDITIONS
    -- 29 NotificationFields TS_NOTIFICATIONFIELDS
    -- 31 NotificationPermissions TS_NOTIFICATIONPERMISSIONS
    -- 32 NotificationSubscriptions TS_NOTIFICATIONSUBSCRIPTIONS
    -- 35 FieldOrderings TS_FIELDORDERINGS
    -- 50 AdminGroups TS_ADMINGROUPS
    -- 56 SystemSettings TS_SYSTEMSETTINGS
    -- 60 Solutions TS_SOLUTIONS
    -- 69 AdminTables TS_ADMINTABLES
    -- 78 TransGroups TS_TRANSGROUPS
    -- 90 UserSolutionData TS_USERSOLUTIONDATA
    -- 108 FieldPrivileges TS_FIELDPRIVILEGES
    -- 120 Roles TS_ROLES
    -- 125 RolePermissions TS_ROLEPERMISSIONS
    -- 126 SecurityControls TS_SECURITYCONTROLS
    -- 128 FormUsages TS_FORMUSAGES
    -- 131 Applications TS_APPLICATIONS
    -- 137 UserFormStates TS_USERFORMSTATES
    -- 138 ProcessModels TS_PROCESSMODELS
    -- 139 ApplicationProcessModels TS_APPLICATIONPROCESSMODELS
    -- 152 DuedateConditions TS_DUEDATECONDITIONS
    -- 166 ImportDataFieldMaps TS_IMPORTDATAFIELDMAPS
    -- 167 ImportDataFieldMapItems TS_IMPORTDATAFIELDMAPITEMS
    -- 189 ApplicationGroups TS_APPLICATION_GROUPS
    -- 195 Feeds TS_FEED
    -- 197 Feed Usages TS_FEEDUSAGES
    -- 221 Templates TS_TEMPLATES
    -- 222 Template Links TS_TEMPLATELINKS
    -- 224 Group Preferences TS_GROUPPREFERENCES
    -- 226 Group Settings TS_GROUPSETTINGS
    The reply is currently minimized Show
Your Reply

Recent Tweets

Twitter response: "Invalid or expired token."