Audit logging

by Otger Cobben on October 17, 2018

In SBM we are missing audit trail for all user administration, like add, remove, modify users and groups and changing rights. This is needed for strict security requirements we have (we are a bank). 

  • To give an idea, this type of events should be logged:

    Logon failed
    Activation of an account which was blocked or suspended or expired.
    A password reset for an account.
    A change in the securty profile settings of an account. These settings can be like increasing the logon attempts of a user, or changing the minimum password length for an account etc.
    User Created
    User Deleted
    Group Created
    Group Deleted
    Group Permissions Changed
    User Added to Group
    User Deleted from Group
    User Lockout
    All unauthorized access attempts
    Use of privileged accounts (admin): log on/log off
    Use of normal accounts: log on/log off

     

  • Please login to view any attachments.

  • I've include some SQL we use to list the TS_ADMINCHANGES table. That has a subset of what you need. It's heavily geared for SQL Server.

    [code type="markup"]
    -- All Admin changes for the last 'N' days

    -- Author: $PauThompson@caci.com$
    -- Date: $10:07 AM 1/26/2016$
    -- File: $$
    -- Script: $AdminChanges.sql$
    -- Requires: $Requires$
    -- Provides: $Provides$
    -- Revision: $Revision$
    -- URL: $URL$
    -- Id: $Id$
    -- Header: $Header$


    -- Query records for the last 'N' Days.
    Declare @DAYS int = 45;

    -- ********************************************************************************************************
    -- TS_AdminChanges only logs changes for a subset of system tables. Here's the list (AFAIK) and the SQL
    -- I used to generate the list :

    -- SELECT
    -- tbl.ts_id,
    -- Cast(tbl.ts_name as varchar(32)) as [Table] ,
    -- Cast(tbl.ts_dbname as varchar(32)) [dB Name]
    -- FROM ts_Tables tbl WHERE tbl.ts_id IN (SELECT DISTINCT (ts_tableid) FROM ts_adminchanges)
    -- ;



    -- ts_id Table dB Name
    -- ----------- -------------------------------- --------------------------------
    -- 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
    -- 52 MailBox TS_MAILBOX
    -- 53 MailMappings TS_MAILMAPPINGS
    -- 55 MSSelection TS_MSSELECTION
    -- 56 SystemSettings TS_SYSTEMSETTINGS
    -- 60 Solutions TS_SOLUTIONS
    -- 69 AdminTables TS_ADMINTABLES
    -- 78 TransGroups TS_TRANSGROUPS
    -- 90 UserSolutionData TS_USERSOLUTIONDATA
    -- 101 SelectionUsages TS_SELECTIONUSAGES
    -- 108 FieldPrivileges TS_FIELDPRIVILEGES
    -- 115 Calendars TS_CALENDARS
    -- 116 CalendarWeekdays TS_CALENDARWEEKDAYS
    -- 117 CalendarOverrides TS_CALENDAROVERRIDES
    -- 118 CalendarTimeRanges TS_CALENDARTIMERANGES
    -- 124 Permissions TS_PERMISSIONS
    -- 125 RolePermissions TS_ROLEPERMISSIONS
    -- 126 SecurityControls TS_SECURITYCONTROLS
    -- 128 FormUsages TS_FORMUSAGES
    -- 130 TransPostProjects TS_TRANSPOSTPROJECTS
    -- 131 Applications TS_APPLICATIONS
    -- 133 ReportDisplayFormats TS_REPORTDISPLAYFORMATS
    -- 137 UserFormStates TS_USERFORMSTATES
    -- 138 ProcessModels TS_PROCESSMODELS
    -- 152 DuedateConditions TS_DUEDATECONDITIONS
    -- 154 AppVariableValues TS_APPVARIABLEVALUES
    -- 155 DecisionRules TS_DECISIONRULES
    -- 156 DecisionsRuleTokens TS_DECISIONRULETOKENS
    -- 158 Entities TS_ENTITIES
    -- 166 ImportDataFieldMaps TS_IMPORTDATAFIELDMAPS
    -- 167 ImportDataFieldMapItems TS_IMPORTDATAFIELDMAPITEMS
    -- 174 LDAPConfigurations TS_LDAPCONFIGURATIONS
    -- 175 Delegations TS_DELEGATIONS
    -- 176 DelegatedItems TS_DELEGATEDITEMS
    -- 182 SystemSettingsNamespaced TS_SYSTEMSETTINGSNAMESPACED
    -- 189 ApplicationGroups TS_APPLICATION_GROUPS
    -- 195 Feeds TS_FEED
    -- 196 Feed Aggregations TS_FEEDAGGREGATION
    -- 197 Feed Usages TS_FEEDUSAGES
    -- 205 Profile Card Overrides TS_PROFILECARDOVERRIDES
    -- 211 SWC View Actions TS_SWCVIEWACTIONS
    -- 221 Templates TS_TEMPLATES
    -- 222 Template Links TS_TEMPLATELINKS
    -- 224 Group Preferences TS_GROUPPREFERENCES
    -- 225 Group Solution Data TS_GROUPSOLUTIONDATA
    -- 226 Group Settings TS_GROUPSETTINGS
    -- 230 MailSubmitRegistries TS_MAILSUBMITREGISTRY
    -- 237 MLS Object Strings TS_MLS_OBJ_STRINGS
    -- 238 MailServers TS_MAILSERVERS
    -- 246 ReportUsage TS_REPORTUSAGE
    -- 251 Folder Shares TS_FOLDERSHARES

    -- ********************************************************************************************************

    -- NOTE: The following is extracted and reformatted from the Serena SBM C++ API file "tsDEF.h"
    -- Note: Deprecated tables: 1, 36, 39, 40, 43, and 70.

    Declare @TS_TBLID_NONE Int = -1 ; -- Default
    Declare @TS_TBLID_CHANGES Int = 2 ;
    Declare @TS_TBLID_FIELDS Int = 3 ;
    Declare @TS_TBLID_GROUPS Int = 4 ;
    Declare @TS_TBLID_MEMBERS Int = 6 ;
    Declare @TS_TBLID_PRIVILEGES Int = 7 ;
    Declare @TS_TBLID_PROJECTS Int = 8 ;
    Declare @TS_TBLID_PROJECTSELECTIONS Int = 9 ;
    Declare @TS_TBLID_PROPERTIES Int = 10 ;
    Declare @TS_TBLID_REPORTS Int = 11 ;
    Declare @TS_TBLID_SELECTIONS Int = 12 ;
    Declare @TS_TBLID_STATES Int = 13 ;
    Declare @TS_TBLID_TRANSISSUETYPES Int = 14 ;
    Declare @TS_TBLID_SYSTEMINFO Int = 15 ;
    Declare @TS_TBLID_TRANSITIONS Int = 16 ;
    Declare @TS_TBLID_USERS Int = 17 ;
    Declare @TS_TBLID_TABLES Int = 18 ;
    Declare @TS_TBLID_ATTACHMENTS Int = 19 ;
    Declare @TS_TBLID_FOLDERS Int = 20 ;
    Declare @TS_TBLID_FOLDERITEMS Int = 21 ;
    Declare @TS_TBLID_VCACTIONS Int = 23 ;
    Declare @TS_TBLID_PROJECTTRANSITIONS Int = 24 ;
    Declare @TS_TBLID_NOTIFICATIONS Int = 25 ;
    Declare @TS_TBLID_NOTIFICATIONRULES Int = 26 ;
    Declare @TS_TBLID_NOTIFICATIONCONDITIONS Int = 27 ;
    Declare @TS_TBLID_NOTIFICATIONEVENTS Int = 28 ;
    Declare @TS_TBLID_NOTIFICATIONFIELDS Int = 29 ;
    Declare @TS_TBLID_NOTIFICATIONMESSAGES Int = 30 ;
    Declare @TS_TBLID_NOTIFICATIONPERMISSIONS Int = 31 ;
    Declare @TS_TBLID_NOTIFICATIONSUBSCRIPTIONS Int = 32 ;
    Declare @TS_TBLID_MACROS Int = 33 ;
    Declare @TS_TBLID_WORKFLOWS Int = 34 ;
    Declare @TS_TBLID_FIELDORDERINGS Int = 35 ;
    Declare @TS_TBLID_COMPANIES Int = 37 ;
    Declare @TS_TBLID_CONTACTS Int = 38 ;
    Declare @TS_TBLID_PROBLEMS Int = 41 ;
    Declare @TS_TBLID_RESOLUTIONS Int = 42 ;
    Declare @TS_TBLID_KEYWORDS Int = 44 ;
    Declare @TS_TBLID_PRODUCTUSAGES Int = 45 ;
    Declare @TS_TBLID_KEYWORDUSAGES Int = 46 ;
    Declare @TS_TBLID_TRANSTRIGGERS Int = 47 ;
    Declare @TS_TBLID_TRANSTRIGGERSTATES Int = 48 ;
    Declare @TS_TBLID_TRANSTRIGGERTRANSITIONS Int = 49 ;
    Declare @TS_TBLID_ADMINGROUPS Int = 50 ;
    Declare @TS_TBLID_MAILHEADERFIELDS Int = 51 ;
    Declare @TS_TBLID_MAILBOX Int = 52 ;
    Declare @TS_TBLID_MAILMAPPINGS Int = 53 ;
    Declare @TS_TBLID_SELFREGFIELDS Int = 54 ;
    Declare @TS_TBLID_MSSELECTION Int = 55 ;
    Declare @TS_TBLID_SYSTEMSETTINGS Int = 56 ;
    Declare @TS_TBLID_GWESTATES Int = 57 ;
    Declare @TS_TBLID_GWETRANSITIONS Int = 58 ;
    Declare @TS_TBLID_GWECOMMENTS Int = 59 ;
    Declare @TS_TBLID_SOLUTIONS Int = 60 ;
    Declare @TS_TBLID_SUBTASKS Int = 61 ;
    Declare @TS_TBLID_SECTIONS Int = 62 ;
    Declare @TS_TBLID_BROWSERS Int = 63 ;
    Declare @TS_TBLID_RESOURCES Int = 64 ;
    Declare @TS_TBLID_BLOBS Int = 65 ;
    Declare @TS_TBLID_RECORDLOCKS Int = 66 ;
    Declare @TS_TBLID_ITEMNOTIFICATIONS Int = 67 ;
    Declare @TS_TBLID_USAGES Int = 68 ;
    Declare @TS_TBLID_ADMINTABLES Int = 69 ;
    Declare @TS_TBLID_ADMINLOCKS Int = 71 ;
    Declare @TS_TBLID_EXTERNALDATABASES Int = 72 ;
    Declare @TS_TBLID_EXTERNALPOSTINGS Int = 73 ;
    Declare @TS_TBLID_EXTERNALPOSTFIELDMAP Int = 74 ;
    Declare @TS_TBLID_ADMINCHANGES Int = 75 ;
    Declare @TS_TBLID_LASTID Int = 76 ;
    Declare @TS_TBLID_LASTIDS Int = 76 ;
    Declare @TS_TBLID_URLSTORE Int = 77 ;
    Declare @TS_TBLID_TRANSGROUPS Int = 78 ;
    Declare @TS_TBLID_ATTACHMENTSEQUENCES Int = 79 ;
    Declare @TS_TBLID_TOOLS Int = 80 ;
    Declare @TS_TBLID_BRIDGES Int = 81 ;
    Declare @TS_TBLID_FIELD_MAP Int = 82 ;
    Declare @TS_TBLID_USER_MAP Int = 83 ;
    Declare @TS_TBLID_XREF Int = 84 ;
    Declare @TS_TBLID_MULTIUSERUSAGES Int = 85 ;
    Declare @TS_TBLID_CONCURRENTLOGINS Int = 86 ;
    Declare @TS_TBLID_LABELS Int = 87 ;
    Declare @TS_TBLID_VCURLFORMATS Int = 88 ;
    Declare @TS_TBLID_TRANSORDERINGS Int = 89 ;
    Declare @TS_TBLID_USERSOLUTIONDATA Int = 90 ;
    Declare @TS_TBLID_USERQUICKLINKS Int = 91 ;
    Declare @TS_TBLID_CACHEACTIONS Int = 92 ;
    Declare @TS_TBLID_TRANSATTRS Int = 93 ;
    Declare @TS_TBLID_TRANSATTRUSAGES Int = 94 ;
    Declare @TS_TBLID_URLINTEGRATIONS Int = 95 ;
    Declare @TS_TBLID_URLUSAGES Int = 96 ;
    Declare @TS_TBLID_LANGUAGES Int = 97 ;
    Declare @TS_TBLID_STRINGIDENTIFIERS Int = 98 ;
    Declare @TS_TBLID_STRINGS Int = 99 ;
    Declare @TS_TBLID_STACTIONS Int = 100 ;
    Declare @TS_TBLID_SELECTIONUSAGES Int = 101 ;
    Declare @TS_TBLID_OBJATTRS Int = 102 ;
    Declare @TS_TBLID_DBSOURCES Int = 103 ;
    Declare @TS_TBLID_IMPORTOPTIONSETS Int = 104 ;
    Declare @TS_TBLID_CHANGEACTIONS Int = 105 ;

    -- Declare @TS_TBLID_CHANGEDETAILS Int = 106 ; -- NOTE: TS_TBLID_CHANGEDETAILS never implemented

    Declare @TS_TBLID_CHANGETEXT Int = 107 ;
    Declare @TS_TBLID_FIELDPRIVILEGES Int = 108 ;
    Declare @TS_TBLID_OLDPASSWORDS Int = 109 ;
    Declare @TS_TBLID_TRANSSIGNS Int = 110 ;
    Declare @TS_TBLID_WSDESCRIPTIONS Int = 111 ;
    Declare @TS_TBLID_WSCALLS Int = 112 ;
    Declare @TS_TBLID_WSSELECTIONMAPPINGS Int = 113 ;
    Declare @TS_TBLID_WSDATAMAPPINGS Int = 114 ;
    Declare @TS_TBLID_CALENDARS Int = 115 ;
    Declare @TS_TBLID_CALENDARWEEKDAYS Int = 116 ;
    Declare @TS_TBLID_CALENDAROVERRIDES Int = 117 ;
    Declare @TS_TBLID_CALENDARTIMERANGES Int = 118 ;
    Declare @TS_TBLID_TIMEINSTATE Int = 119 ;
    Declare @TS_TBLID_ROLES Int = 120 ;
    Declare @TS_TBLID_PERMISSIONACTIONS Int = 121 ;
    Declare @TS_TBLID_PERMISSIONOBJECTS Int = 122 ;
    Declare @TS_TBLID_PERMISSIONCONDITIONS Int = 123 ;
    Declare @TS_TBLID_PERMISSIONS Int = 124 ;
    Declare @TS_TBLID_ROLEPERMISSIONS Int = 125 ;
    Declare @TS_TBLID_SECURITYCONTROLS Int = 126 ;
    Declare @TS_TBLID_FORMS Int = 127 ;
    Declare @TS_TBLID_FORMUSAGES Int = 128 ;
    Declare @TS_TBLID_FORMUSERPREF Int = 129 ;
    Declare @TS_TBLID_TRANSITIONPOSTPROJECTS Int = 130 ;
    Declare @TS_TBLID_APPLICATIONS Int = 131 ;
    Declare @TS_TBLID_IMPORTSTATUS Int = 132 ;
    Declare @TS_TBLID_REPORTDISPLAYFORMATS Int = 133 ;
    Declare @TS_TBLID_ALFEVENTS Int = 134 ;
    Declare @TS_TBLID_ALFEVENTCALLS Int = 135 ;
    Declare @TS_TBLID_DELETEDOBJECTS Int = 136 ;
    Declare @TS_TBLID_USERFORMSTATES Int = 137 ;
    Declare @TS_TBLID_PROCESSMODELS Int = 138 ;
    Declare @TS_TBLID_APPLICATIONPROCESSMODELS Int = 139 ;
    Declare @TS_TBLID_FORMSTRINGS Int = 140 ;
    Declare @TS_TBLID_WSXSDFILES Int = 141 ;
    Declare @TS_TBLID_NAMESPACES Int = 142 ;
    Declare @TS_TBLID_PROCESSMODELREFERENCES Int = 143 ;
    Declare @TS_TBLID_UNRESOLVEDOBJECTS Int = 144 ;
    Declare @TS_TBLID_UNRESOLVEDOBJECTREFS Int = 145 ;
    Declare @TS_TBLID_SHAREPOINTSERVERS Int = 146 ;
    Declare @TS_TBLID_SHAREPOINTPROJECTSERVERS Int = 147 ;
    Declare @TS_TBLID_USERSETTINGS Int = 148 ;
    Declare @TS_TBLID_CALENDARDENORM Int = 149 ;
    Declare @TS_TBLID_PROJECTANCESTRYDENORM Int = 150 ;
    Declare @TS_TBLID_WORKFLOWANCESTRYDENORM Int = 151 ;
    Declare @TS_TBLID_DUEDATECONDITIONS Int = 152 ;
    Declare @TS_TBLID_APPVARIABLES Int = 153 ;
    Declare @TS_TBLID_APPVARIABLEVALUES Int = 154 ;
    Declare @TS_TBLID_DECISIONRULES Int = 155 ;
    Declare @TS_TBLID_DECISIONRULETOKENS Int = 156 ;
    Declare @TS_TBLID_GWEBANDS Int = 157 ;
    Declare @TS_TBLID_ENTITIES Int = 158 ;
    Declare @TS_TBLID_ENTITYPROPERTIES Int = 159 ;
    Declare @TS_TBLID_ENTITYVALUES Int = 160 ;
    Declare @TS_TBLID_TRACEWORKS Int = 161 ;
    Declare @TS_TBLID_PHASES Int = 162 ;
    Declare @TS_TBLID_TRANSEXCLUSIONS Int = 163 ;
    Declare @TS_TBLID_PAUSEDITEMS Int = 164 ;
    Declare @TS_TBLID_PAUSEDREASONS Int = 165 ;
    Declare @TS_TBLID_IMPORTDATAFIELDMAPS Int = 166 ;
    Declare @TS_TBLID_IMPORTDATAFIELDMAPITEMS Int = 167 ;
    Declare @TS_TBLID_TRANSITIONDATAMAPPINGS Int = 168 ;
    Declare @TS_TBLID_PLUGINS Int = 169 ;
    Declare @TS_TBLID_CHANNELS Int = 170 ;
    Declare @TS_TBLID_PLUGINVARS Int = 171 ;
    Declare @TS_TBLID_PLUGINCONTEXT Int = 172 ;
    Declare @TS_TBLID_NOTIFICATIONCHANNELS Int = 173 ;
    Declare @TS_TBLID_LDAPCONFIGURATIONS Int = 174 ;
    Declare @TS_TBLID_DELEGATIONS Int = 175 ;
    Declare @TS_TBLID_DELEGATEDITEMS Int = 176 ;
    Declare @TS_TBLID_TIMECAPTURE Int = 177 ;
    Declare @TS_TBLID_TIMECAPTURE_DENORM Int = 178 ;
    Declare @TS_TBLID_TIMECAPTURE_ROLLUP Int = 179 ;
    Declare @TS_TBLID_WF_PRJ_OVERRIDES Int = 180 ;
    Declare @TS_TBLID_FOLLOWERNOTIFICATIONS Int = 181 ;
    Declare @TS_TBLID_SYSTEMSETTINGSNAMESPACED Int = 182 ;
    Declare @TS_TBLID_THEMES Int = 183 ;
    Declare @TS_TBLID_TIMEZONEGMTOFFSETDENORM Int = 184 ;
    Declare @TS_TBLID_NOTIFICATIONRESPONSES Int = 185 ;
    Declare @TS_TBLID_NOTIFICATIONTOKENS Int = 186 ;
    Declare @TS_TBLID_BUSINESS_SOLUTIONS Int = 187 ;
    Declare @TS_TBLID_BUSINESS_SOLUTION_REFS Int = 188 ;
    Declare @TS_TBLID_APPLICATION_GROUPS Int = 189 ;
    Declare @TS_TBLID_APPLICATION_GROUP_REFS Int = 190 ;
    Declare @TS_TBLID_USER_SUBMITHISTORY Int = 191 ;
    Declare @TS_TBLID_USER_VIEWHISTORY Int = 192 ;
    Declare @TS_TBLID_UINOTIFICATIONS Int = 193 ;
    Declare @TS_TBLID_LDAPSCHEDULES Int = 194 ;
    Declare @TS_TBLID_FEED Int = 195 ;
    Declare @TS_TBLID_FEEDAGGREGATION Int = 196 ;
    Declare @TS_TBLID_FEEDUSAGES Int = 197 ;
    Declare @TS_TBLID_PROJECTSETTINGS Int = 198 ;
    Declare @TS_TBLID_RSM_TEAM Int = 199 ;
    Declare @TS_TBLID_RSM_TEAMMEMBER Int = 200 ;
    Declare @TS_TBLID_RSM_RESOURCE Int = 201 ;
    Declare @TS_TBLID_RSM_ATTRIBUTE Int = 202 ;
    Declare @TS_TBLID_RSM_JOBFUNCTION Int = 203 ;
    Declare @TS_TBLID_RSM_SKILL Int = 204 ;
    Declare @TS_TBLID_PROFILECARDOVERRIDES Int = 205 ;
    Declare @TS_TBLID_FEEDMAPPINGS Int = 206 ;
    Declare @TS_TBLID_AGILEBACKLOGS Int = 207 ;
    Declare @TS_TBLID_AGILEITEMS Int = 208 ;
    Declare @TS_TBLID_SWCVIEWS Int = 209 ;
    Declare @TS_TBLID_SWCVIEWSHARES Int = 210 ;
    Declare @TS_TBLID_SWCVIEWACTIONS Int = 211 ;
    Declare @TS_TBLID_AGILECHANGEACTIONS Int = 212 ;
    Declare @TS_TBLID_SWC_PROFILE Int = 213 ;
    Declare @TS_TBLID_SWC_PROPERTY Int = 214 ;
    Declare @TS_TBLID_SWC_USER_DATA Int = 215 ;
    Declare @TS_TBLID_SWC_LAYOUT Int = 216 ;
    Declare @TS_TBLID_SWC_MENUITEM Int = 217 ;
    Declare @TS_TBLID_SWC_MENUITEM_OVERRIDE Int = 218 ;
    Declare @TS_TBLID_SWC_MENUITEM_REFERENCE Int = 219 ;
    Declare @TS_TBLID_SWC_MENUITEM_SEQUENCE Int = 220 ;
    Declare @TS_TBLID_TEMPLATES Int = 221 ;
    Declare @TS_TBLID_TEMPLATELINKS Int = 222 ;
    Declare @TS_TBLID_FEEDAGGREGATIONFIELD Int = 223 ;
    Declare @TS_TBLID_GROUPPREFERENCES Int = 224 ;
    Declare @TS_TBLID_GROUPSOLUTIONDATA Int = 225 ;
    Declare @TS_TBLID_GROUPSETTINGS Int = 226 ;
    Declare @TS_TBLID_REL_ADMINCHANGES Int = 227 ;
    Declare @TS_TBLID_REL_CHANGEACTIONS Int = 228 ;
    Declare @TS_TBLID_VIEWSTATUSNOTIFICATION Int = 229 ;
    Declare @TS_TBLID_MAILSUBMITREGISTRY Int = 230 ;
    Declare @TS_TBLID_RESTDATASOURCE Int = 231 ;
    Declare @TS_TBLID_KANBANVIEW Int = 232 ;
    Declare @TS_TBLID_USERTIMES Int = 233 ;
    Declare @TS_TBLID_LOCALES Int = 234 ;
    Declare @TS_TBLID_LOCALE_ANCESTRYDENORM Int = 235 ;
    Declare @TS_TBLID_MLS_OBJS Int = 236 ;
    Declare @TS_TBLID_MLS_OBJ_STRINGS Int = 237 ;
    Declare @TS_TBLID_MAILSERVERS Int = 238 ;
    Declare @TS_TBLID_FILE_OBJS Int = 239 ;
    Declare @TS_TBLID_FILE_OBJS_TEMP Int = 240 ;
    Declare @TS_TBLID_FILE_OBJS_CHANGE Int = 241 ;
    Declare @TS_TBLID_SWC_MENUITEM_SEQ_ADMIN Int = 242 ;
    Declare @TS_TBLID_SWC_MENUITEM_INH_ORDER Int = 243 ;
    Declare @TS_TBLID_MAILSUBMITROUTES Int = 244 ;
    Declare @TS_TBLID_ROUTECONDITIONS Int = 245 ;
    Declare @TS_TBLID_REPORT_USAGE Int = 246 ;
    Declare @TS_TBLID_REPORT_CHANGEACTIONS Int = 247 ;
    Declare @TS_TBLID_REPORT_CHANGES Int = 248 ;
    Declare @TS_TBLID_EXTERNAL_USERS Int = 249 ;
    Declare @TS_TBLID_EXTERNAL_USER_RULES Int = 250 ;
    Declare @TS_TBLID_FOLDERSHARES Int = 251 ;

    -- Solution Framework tables.
    -- The entire 300 series of tableids are reserved for the Solution Framework
    -- These SHOULD NOT be used for platform table ids EJR

    Declare @TS_TBLID_SOLUTIONFRAMEWORK_001 Int = 300 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_002 Int = 301 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_003 Int = 302 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_004 Int = 303 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_005 Int = 304 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_006 Int = 305 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_007 Int = 306 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_008 Int = 307 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_009 Int = 308 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_010 Int = 309 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_011 Int = 310 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_012 Int = 311 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_013 Int = 312 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_014 Int = 313 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_015 Int = 314 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_016 Int = 315 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_017 Int = 316 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_018 Int = 317 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_019 Int = 318 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_020 Int = 319 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_021 Int = 320 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_022 Int = 321 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_023 Int = 322 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_024 Int = 323 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_025 Int = 324 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_026 Int = 325 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_027 Int = 326 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_028 Int = 327 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_029 Int = 328 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_030 Int = 329 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_031 Int = 330 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_032 Int = 331 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_033 Int = 332 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_034 Int = 333 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_035 Int = 334 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_036 Int = 335 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_037 Int = 336 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_038 Int = 337 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_039 Int = 338 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_040 Int = 339 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_041 Int = 340 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_042 Int = 341 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_043 Int = 342 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_044 Int = 343 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_045 Int = 344 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_046 Int = 345 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_047 Int = 346 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_048 Int = 347 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_049 Int = 348 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_050 Int = 349 ;
    -- Table Id values between 349 and 398 are reserved for Solution Framework tables.
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_099 Int = 398 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_100 Int = 399 ;




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

    -- 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] ,

    -- Value-to-text mapping also from tsDEF.h
    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(64)) As [Name on record changed]


    -- TODO
    -- Add another column to compose (if possible) the "value" that was changed in the selected table.
    -- This would require a CASE on TS_TABLEID and lookups of the Name / Title / Value from each.
    -- .... a lot of work ... Mayby only do it for "important" tables or where the displayed result
    -- would make sense add clarity ...

    From
    ts_AdminChanges admchg
    Where
    -- For the last XX days
    (DateDiff(dd,DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970'),GetUtcDate())) < @DAYS

    -- Ignoring these 3 tables eliminates about 90% of the "noise" in the output.
    -- These tables seem to be related to stuff end-users are doing -- not administrative changes.
    AND (admchg.TS_TABLEID Not In (@TS_TBLID_REPORT_USAGE,@TS_TBLID_FIELDORDERINGS,@TS_TBLID_USERFORMSTATES) )
    Order By
    admchg.TS_TIMESTAMP
    ;
    [/code]


    Paul Thompson Commented by Paul Thompson November 02, 2018
    Top 50 Reviewer  -  

    I've include some SQL we use to list the TS_ADMINCHANGES table. That has a subset of what you need. It's heavily geared for SQL Server.

    [code type="markup"]
    -- All Admin changes for the last 'N' days

    -- Author: $PauThompson@caci.com$
    -- Date: $10:07 AM 1/26/2016$
    -- File: $$
    -- Script: $AdminChanges.sql$
    -- Requires: $Requires$
    -- Provides: $Provides$
    -- Revision: $Revision$
    -- URL: $URL$
    -- Id: $Id$
    -- Header: $Header$


    -- Query records for the last 'N' Days.
    Declare @DAYS int = 45;

    -- ********************************************************************************************************
    -- TS_AdminChanges only logs changes for a subset of system tables. Here's the list (AFAIK) and the SQL
    -- I used to generate the list :

    -- SELECT
    -- tbl.ts_id,
    -- Cast(tbl.ts_name as varchar(32)) as [Table] ,
    -- Cast(tbl.ts_dbname as varchar(32)) [dB Name]
    -- FROM ts_Tables tbl WHERE tbl.ts_id IN (SELECT DISTINCT (ts_tableid) FROM ts_adminchanges)
    -- ;



    -- ts_id Table dB Name
    -- ----------- -------------------------------- --------------------------------
    -- 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
    -- 52 MailBox TS_MAILBOX
    -- 53 MailMappings TS_MAILMAPPINGS
    -- 55 MSSelection TS_MSSELECTION
    -- 56 SystemSettings TS_SYSTEMSETTINGS
    -- 60 Solutions TS_SOLUTIONS
    -- 69 AdminTables TS_ADMINTABLES
    -- 78 TransGroups TS_TRANSGROUPS
    -- 90 UserSolutionData TS_USERSOLUTIONDATA
    -- 101 SelectionUsages TS_SELECTIONUSAGES
    -- 108 FieldPrivileges TS_FIELDPRIVILEGES
    -- 115 Calendars TS_CALENDARS
    -- 116 CalendarWeekdays TS_CALENDARWEEKDAYS
    -- 117 CalendarOverrides TS_CALENDAROVERRIDES
    -- 118 CalendarTimeRanges TS_CALENDARTIMERANGES
    -- 124 Permissions TS_PERMISSIONS
    -- 125 RolePermissions TS_ROLEPERMISSIONS
    -- 126 SecurityControls TS_SECURITYCONTROLS
    -- 128 FormUsages TS_FORMUSAGES
    -- 130 TransPostProjects TS_TRANSPOSTPROJECTS
    -- 131 Applications TS_APPLICATIONS
    -- 133 ReportDisplayFormats TS_REPORTDISPLAYFORMATS
    -- 137 UserFormStates TS_USERFORMSTATES
    -- 138 ProcessModels TS_PROCESSMODELS
    -- 152 DuedateConditions TS_DUEDATECONDITIONS
    -- 154 AppVariableValues TS_APPVARIABLEVALUES
    -- 155 DecisionRules TS_DECISIONRULES
    -- 156 DecisionsRuleTokens TS_DECISIONRULETOKENS
    -- 158 Entities TS_ENTITIES
    -- 166 ImportDataFieldMaps TS_IMPORTDATAFIELDMAPS
    -- 167 ImportDataFieldMapItems TS_IMPORTDATAFIELDMAPITEMS
    -- 174 LDAPConfigurations TS_LDAPCONFIGURATIONS
    -- 175 Delegations TS_DELEGATIONS
    -- 176 DelegatedItems TS_DELEGATEDITEMS
    -- 182 SystemSettingsNamespaced TS_SYSTEMSETTINGSNAMESPACED
    -- 189 ApplicationGroups TS_APPLICATION_GROUPS
    -- 195 Feeds TS_FEED
    -- 196 Feed Aggregations TS_FEEDAGGREGATION
    -- 197 Feed Usages TS_FEEDUSAGES
    -- 205 Profile Card Overrides TS_PROFILECARDOVERRIDES
    -- 211 SWC View Actions TS_SWCVIEWACTIONS
    -- 221 Templates TS_TEMPLATES
    -- 222 Template Links TS_TEMPLATELINKS
    -- 224 Group Preferences TS_GROUPPREFERENCES
    -- 225 Group Solution Data TS_GROUPSOLUTIONDATA
    -- 226 Group Settings TS_GROUPSETTINGS
    -- 230 MailSubmitRegistries TS_MAILSUBMITREGISTRY
    -- 237 MLS Object Strings TS_MLS_OBJ_STRINGS
    -- 238 MailServers TS_MAILSERVERS
    -- 246 ReportUsage TS_REPORTUSAGE
    -- 251 Folder Shares TS_FOLDERSHARES

    -- ********************************************************************************************************

    -- NOTE: The following is extracted and reformatted from the Serena SBM C++ API file "tsDEF.h"
    -- Note: Deprecated tables: 1, 36, 39, 40, 43, and 70.

    Declare @TS_TBLID_NONE Int = -1 ; -- Default
    Declare @TS_TBLID_CHANGES Int = 2 ;
    Declare @TS_TBLID_FIELDS Int = 3 ;
    Declare @TS_TBLID_GROUPS Int = 4 ;
    Declare @TS_TBLID_MEMBERS Int = 6 ;
    Declare @TS_TBLID_PRIVILEGES Int = 7 ;
    Declare @TS_TBLID_PROJECTS Int = 8 ;
    Declare @TS_TBLID_PROJECTSELECTIONS Int = 9 ;
    Declare @TS_TBLID_PROPERTIES Int = 10 ;
    Declare @TS_TBLID_REPORTS Int = 11 ;
    Declare @TS_TBLID_SELECTIONS Int = 12 ;
    Declare @TS_TBLID_STATES Int = 13 ;
    Declare @TS_TBLID_TRANSISSUETYPES Int = 14 ;
    Declare @TS_TBLID_SYSTEMINFO Int = 15 ;
    Declare @TS_TBLID_TRANSITIONS Int = 16 ;
    Declare @TS_TBLID_USERS Int = 17 ;
    Declare @TS_TBLID_TABLES Int = 18 ;
    Declare @TS_TBLID_ATTACHMENTS Int = 19 ;
    Declare @TS_TBLID_FOLDERS Int = 20 ;
    Declare @TS_TBLID_FOLDERITEMS Int = 21 ;
    Declare @TS_TBLID_VCACTIONS Int = 23 ;
    Declare @TS_TBLID_PROJECTTRANSITIONS Int = 24 ;
    Declare @TS_TBLID_NOTIFICATIONS Int = 25 ;
    Declare @TS_TBLID_NOTIFICATIONRULES Int = 26 ;
    Declare @TS_TBLID_NOTIFICATIONCONDITIONS Int = 27 ;
    Declare @TS_TBLID_NOTIFICATIONEVENTS Int = 28 ;
    Declare @TS_TBLID_NOTIFICATIONFIELDS Int = 29 ;
    Declare @TS_TBLID_NOTIFICATIONMESSAGES Int = 30 ;
    Declare @TS_TBLID_NOTIFICATIONPERMISSIONS Int = 31 ;
    Declare @TS_TBLID_NOTIFICATIONSUBSCRIPTIONS Int = 32 ;
    Declare @TS_TBLID_MACROS Int = 33 ;
    Declare @TS_TBLID_WORKFLOWS Int = 34 ;
    Declare @TS_TBLID_FIELDORDERINGS Int = 35 ;
    Declare @TS_TBLID_COMPANIES Int = 37 ;
    Declare @TS_TBLID_CONTACTS Int = 38 ;
    Declare @TS_TBLID_PROBLEMS Int = 41 ;
    Declare @TS_TBLID_RESOLUTIONS Int = 42 ;
    Declare @TS_TBLID_KEYWORDS Int = 44 ;
    Declare @TS_TBLID_PRODUCTUSAGES Int = 45 ;
    Declare @TS_TBLID_KEYWORDUSAGES Int = 46 ;
    Declare @TS_TBLID_TRANSTRIGGERS Int = 47 ;
    Declare @TS_TBLID_TRANSTRIGGERSTATES Int = 48 ;
    Declare @TS_TBLID_TRANSTRIGGERTRANSITIONS Int = 49 ;
    Declare @TS_TBLID_ADMINGROUPS Int = 50 ;
    Declare @TS_TBLID_MAILHEADERFIELDS Int = 51 ;
    Declare @TS_TBLID_MAILBOX Int = 52 ;
    Declare @TS_TBLID_MAILMAPPINGS Int = 53 ;
    Declare @TS_TBLID_SELFREGFIELDS Int = 54 ;
    Declare @TS_TBLID_MSSELECTION Int = 55 ;
    Declare @TS_TBLID_SYSTEMSETTINGS Int = 56 ;
    Declare @TS_TBLID_GWESTATES Int = 57 ;
    Declare @TS_TBLID_GWETRANSITIONS Int = 58 ;
    Declare @TS_TBLID_GWECOMMENTS Int = 59 ;
    Declare @TS_TBLID_SOLUTIONS Int = 60 ;
    Declare @TS_TBLID_SUBTASKS Int = 61 ;
    Declare @TS_TBLID_SECTIONS Int = 62 ;
    Declare @TS_TBLID_BROWSERS Int = 63 ;
    Declare @TS_TBLID_RESOURCES Int = 64 ;
    Declare @TS_TBLID_BLOBS Int = 65 ;
    Declare @TS_TBLID_RECORDLOCKS Int = 66 ;
    Declare @TS_TBLID_ITEMNOTIFICATIONS Int = 67 ;
    Declare @TS_TBLID_USAGES Int = 68 ;
    Declare @TS_TBLID_ADMINTABLES Int = 69 ;
    Declare @TS_TBLID_ADMINLOCKS Int = 71 ;
    Declare @TS_TBLID_EXTERNALDATABASES Int = 72 ;
    Declare @TS_TBLID_EXTERNALPOSTINGS Int = 73 ;
    Declare @TS_TBLID_EXTERNALPOSTFIELDMAP Int = 74 ;
    Declare @TS_TBLID_ADMINCHANGES Int = 75 ;
    Declare @TS_TBLID_LASTID Int = 76 ;
    Declare @TS_TBLID_LASTIDS Int = 76 ;
    Declare @TS_TBLID_URLSTORE Int = 77 ;
    Declare @TS_TBLID_TRANSGROUPS Int = 78 ;
    Declare @TS_TBLID_ATTACHMENTSEQUENCES Int = 79 ;
    Declare @TS_TBLID_TOOLS Int = 80 ;
    Declare @TS_TBLID_BRIDGES Int = 81 ;
    Declare @TS_TBLID_FIELD_MAP Int = 82 ;
    Declare @TS_TBLID_USER_MAP Int = 83 ;
    Declare @TS_TBLID_XREF Int = 84 ;
    Declare @TS_TBLID_MULTIUSERUSAGES Int = 85 ;
    Declare @TS_TBLID_CONCURRENTLOGINS Int = 86 ;
    Declare @TS_TBLID_LABELS Int = 87 ;
    Declare @TS_TBLID_VCURLFORMATS Int = 88 ;
    Declare @TS_TBLID_TRANSORDERINGS Int = 89 ;
    Declare @TS_TBLID_USERSOLUTIONDATA Int = 90 ;
    Declare @TS_TBLID_USERQUICKLINKS Int = 91 ;
    Declare @TS_TBLID_CACHEACTIONS Int = 92 ;
    Declare @TS_TBLID_TRANSATTRS Int = 93 ;
    Declare @TS_TBLID_TRANSATTRUSAGES Int = 94 ;
    Declare @TS_TBLID_URLINTEGRATIONS Int = 95 ;
    Declare @TS_TBLID_URLUSAGES Int = 96 ;
    Declare @TS_TBLID_LANGUAGES Int = 97 ;
    Declare @TS_TBLID_STRINGIDENTIFIERS Int = 98 ;
    Declare @TS_TBLID_STRINGS Int = 99 ;
    Declare @TS_TBLID_STACTIONS Int = 100 ;
    Declare @TS_TBLID_SELECTIONUSAGES Int = 101 ;
    Declare @TS_TBLID_OBJATTRS Int = 102 ;
    Declare @TS_TBLID_DBSOURCES Int = 103 ;
    Declare @TS_TBLID_IMPORTOPTIONSETS Int = 104 ;
    Declare @TS_TBLID_CHANGEACTIONS Int = 105 ;

    -- Declare @TS_TBLID_CHANGEDETAILS Int = 106 ; -- NOTE: TS_TBLID_CHANGEDETAILS never implemented

    Declare @TS_TBLID_CHANGETEXT Int = 107 ;
    Declare @TS_TBLID_FIELDPRIVILEGES Int = 108 ;
    Declare @TS_TBLID_OLDPASSWORDS Int = 109 ;
    Declare @TS_TBLID_TRANSSIGNS Int = 110 ;
    Declare @TS_TBLID_WSDESCRIPTIONS Int = 111 ;
    Declare @TS_TBLID_WSCALLS Int = 112 ;
    Declare @TS_TBLID_WSSELECTIONMAPPINGS Int = 113 ;
    Declare @TS_TBLID_WSDATAMAPPINGS Int = 114 ;
    Declare @TS_TBLID_CALENDARS Int = 115 ;
    Declare @TS_TBLID_CALENDARWEEKDAYS Int = 116 ;
    Declare @TS_TBLID_CALENDAROVERRIDES Int = 117 ;
    Declare @TS_TBLID_CALENDARTIMERANGES Int = 118 ;
    Declare @TS_TBLID_TIMEINSTATE Int = 119 ;
    Declare @TS_TBLID_ROLES Int = 120 ;
    Declare @TS_TBLID_PERMISSIONACTIONS Int = 121 ;
    Declare @TS_TBLID_PERMISSIONOBJECTS Int = 122 ;
    Declare @TS_TBLID_PERMISSIONCONDITIONS Int = 123 ;
    Declare @TS_TBLID_PERMISSIONS Int = 124 ;
    Declare @TS_TBLID_ROLEPERMISSIONS Int = 125 ;
    Declare @TS_TBLID_SECURITYCONTROLS Int = 126 ;
    Declare @TS_TBLID_FORMS Int = 127 ;
    Declare @TS_TBLID_FORMUSAGES Int = 128 ;
    Declare @TS_TBLID_FORMUSERPREF Int = 129 ;
    Declare @TS_TBLID_TRANSITIONPOSTPROJECTS Int = 130 ;
    Declare @TS_TBLID_APPLICATIONS Int = 131 ;
    Declare @TS_TBLID_IMPORTSTATUS Int = 132 ;
    Declare @TS_TBLID_REPORTDISPLAYFORMATS Int = 133 ;
    Declare @TS_TBLID_ALFEVENTS Int = 134 ;
    Declare @TS_TBLID_ALFEVENTCALLS Int = 135 ;
    Declare @TS_TBLID_DELETEDOBJECTS Int = 136 ;
    Declare @TS_TBLID_USERFORMSTATES Int = 137 ;
    Declare @TS_TBLID_PROCESSMODELS Int = 138 ;
    Declare @TS_TBLID_APPLICATIONPROCESSMODELS Int = 139 ;
    Declare @TS_TBLID_FORMSTRINGS Int = 140 ;
    Declare @TS_TBLID_WSXSDFILES Int = 141 ;
    Declare @TS_TBLID_NAMESPACES Int = 142 ;
    Declare @TS_TBLID_PROCESSMODELREFERENCES Int = 143 ;
    Declare @TS_TBLID_UNRESOLVEDOBJECTS Int = 144 ;
    Declare @TS_TBLID_UNRESOLVEDOBJECTREFS Int = 145 ;
    Declare @TS_TBLID_SHAREPOINTSERVERS Int = 146 ;
    Declare @TS_TBLID_SHAREPOINTPROJECTSERVERS Int = 147 ;
    Declare @TS_TBLID_USERSETTINGS Int = 148 ;
    Declare @TS_TBLID_CALENDARDENORM Int = 149 ;
    Declare @TS_TBLID_PROJECTANCESTRYDENORM Int = 150 ;
    Declare @TS_TBLID_WORKFLOWANCESTRYDENORM Int = 151 ;
    Declare @TS_TBLID_DUEDATECONDITIONS Int = 152 ;
    Declare @TS_TBLID_APPVARIABLES Int = 153 ;
    Declare @TS_TBLID_APPVARIABLEVALUES Int = 154 ;
    Declare @TS_TBLID_DECISIONRULES Int = 155 ;
    Declare @TS_TBLID_DECISIONRULETOKENS Int = 156 ;
    Declare @TS_TBLID_GWEBANDS Int = 157 ;
    Declare @TS_TBLID_ENTITIES Int = 158 ;
    Declare @TS_TBLID_ENTITYPROPERTIES Int = 159 ;
    Declare @TS_TBLID_ENTITYVALUES Int = 160 ;
    Declare @TS_TBLID_TRACEWORKS Int = 161 ;
    Declare @TS_TBLID_PHASES Int = 162 ;
    Declare @TS_TBLID_TRANSEXCLUSIONS Int = 163 ;
    Declare @TS_TBLID_PAUSEDITEMS Int = 164 ;
    Declare @TS_TBLID_PAUSEDREASONS Int = 165 ;
    Declare @TS_TBLID_IMPORTDATAFIELDMAPS Int = 166 ;
    Declare @TS_TBLID_IMPORTDATAFIELDMAPITEMS Int = 167 ;
    Declare @TS_TBLID_TRANSITIONDATAMAPPINGS Int = 168 ;
    Declare @TS_TBLID_PLUGINS Int = 169 ;
    Declare @TS_TBLID_CHANNELS Int = 170 ;
    Declare @TS_TBLID_PLUGINVARS Int = 171 ;
    Declare @TS_TBLID_PLUGINCONTEXT Int = 172 ;
    Declare @TS_TBLID_NOTIFICATIONCHANNELS Int = 173 ;
    Declare @TS_TBLID_LDAPCONFIGURATIONS Int = 174 ;
    Declare @TS_TBLID_DELEGATIONS Int = 175 ;
    Declare @TS_TBLID_DELEGATEDITEMS Int = 176 ;
    Declare @TS_TBLID_TIMECAPTURE Int = 177 ;
    Declare @TS_TBLID_TIMECAPTURE_DENORM Int = 178 ;
    Declare @TS_TBLID_TIMECAPTURE_ROLLUP Int = 179 ;
    Declare @TS_TBLID_WF_PRJ_OVERRIDES Int = 180 ;
    Declare @TS_TBLID_FOLLOWERNOTIFICATIONS Int = 181 ;
    Declare @TS_TBLID_SYSTEMSETTINGSNAMESPACED Int = 182 ;
    Declare @TS_TBLID_THEMES Int = 183 ;
    Declare @TS_TBLID_TIMEZONEGMTOFFSETDENORM Int = 184 ;
    Declare @TS_TBLID_NOTIFICATIONRESPONSES Int = 185 ;
    Declare @TS_TBLID_NOTIFICATIONTOKENS Int = 186 ;
    Declare @TS_TBLID_BUSINESS_SOLUTIONS Int = 187 ;
    Declare @TS_TBLID_BUSINESS_SOLUTION_REFS Int = 188 ;
    Declare @TS_TBLID_APPLICATION_GROUPS Int = 189 ;
    Declare @TS_TBLID_APPLICATION_GROUP_REFS Int = 190 ;
    Declare @TS_TBLID_USER_SUBMITHISTORY Int = 191 ;
    Declare @TS_TBLID_USER_VIEWHISTORY Int = 192 ;
    Declare @TS_TBLID_UINOTIFICATIONS Int = 193 ;
    Declare @TS_TBLID_LDAPSCHEDULES Int = 194 ;
    Declare @TS_TBLID_FEED Int = 195 ;
    Declare @TS_TBLID_FEEDAGGREGATION Int = 196 ;
    Declare @TS_TBLID_FEEDUSAGES Int = 197 ;
    Declare @TS_TBLID_PROJECTSETTINGS Int = 198 ;
    Declare @TS_TBLID_RSM_TEAM Int = 199 ;
    Declare @TS_TBLID_RSM_TEAMMEMBER Int = 200 ;
    Declare @TS_TBLID_RSM_RESOURCE Int = 201 ;
    Declare @TS_TBLID_RSM_ATTRIBUTE Int = 202 ;
    Declare @TS_TBLID_RSM_JOBFUNCTION Int = 203 ;
    Declare @TS_TBLID_RSM_SKILL Int = 204 ;
    Declare @TS_TBLID_PROFILECARDOVERRIDES Int = 205 ;
    Declare @TS_TBLID_FEEDMAPPINGS Int = 206 ;
    Declare @TS_TBLID_AGILEBACKLOGS Int = 207 ;
    Declare @TS_TBLID_AGILEITEMS Int = 208 ;
    Declare @TS_TBLID_SWCVIEWS Int = 209 ;
    Declare @TS_TBLID_SWCVIEWSHARES Int = 210 ;
    Declare @TS_TBLID_SWCVIEWACTIONS Int = 211 ;
    Declare @TS_TBLID_AGILECHANGEACTIONS Int = 212 ;
    Declare @TS_TBLID_SWC_PROFILE Int = 213 ;
    Declare @TS_TBLID_SWC_PROPERTY Int = 214 ;
    Declare @TS_TBLID_SWC_USER_DATA Int = 215 ;
    Declare @TS_TBLID_SWC_LAYOUT Int = 216 ;
    Declare @TS_TBLID_SWC_MENUITEM Int = 217 ;
    Declare @TS_TBLID_SWC_MENUITEM_OVERRIDE Int = 218 ;
    Declare @TS_TBLID_SWC_MENUITEM_REFERENCE Int = 219 ;
    Declare @TS_TBLID_SWC_MENUITEM_SEQUENCE Int = 220 ;
    Declare @TS_TBLID_TEMPLATES Int = 221 ;
    Declare @TS_TBLID_TEMPLATELINKS Int = 222 ;
    Declare @TS_TBLID_FEEDAGGREGATIONFIELD Int = 223 ;
    Declare @TS_TBLID_GROUPPREFERENCES Int = 224 ;
    Declare @TS_TBLID_GROUPSOLUTIONDATA Int = 225 ;
    Declare @TS_TBLID_GROUPSETTINGS Int = 226 ;
    Declare @TS_TBLID_REL_ADMINCHANGES Int = 227 ;
    Declare @TS_TBLID_REL_CHANGEACTIONS Int = 228 ;
    Declare @TS_TBLID_VIEWSTATUSNOTIFICATION Int = 229 ;
    Declare @TS_TBLID_MAILSUBMITREGISTRY Int = 230 ;
    Declare @TS_TBLID_RESTDATASOURCE Int = 231 ;
    Declare @TS_TBLID_KANBANVIEW Int = 232 ;
    Declare @TS_TBLID_USERTIMES Int = 233 ;
    Declare @TS_TBLID_LOCALES Int = 234 ;
    Declare @TS_TBLID_LOCALE_ANCESTRYDENORM Int = 235 ;
    Declare @TS_TBLID_MLS_OBJS Int = 236 ;
    Declare @TS_TBLID_MLS_OBJ_STRINGS Int = 237 ;
    Declare @TS_TBLID_MAILSERVERS Int = 238 ;
    Declare @TS_TBLID_FILE_OBJS Int = 239 ;
    Declare @TS_TBLID_FILE_OBJS_TEMP Int = 240 ;
    Declare @TS_TBLID_FILE_OBJS_CHANGE Int = 241 ;
    Declare @TS_TBLID_SWC_MENUITEM_SEQ_ADMIN Int = 242 ;
    Declare @TS_TBLID_SWC_MENUITEM_INH_ORDER Int = 243 ;
    Declare @TS_TBLID_MAILSUBMITROUTES Int = 244 ;
    Declare @TS_TBLID_ROUTECONDITIONS Int = 245 ;
    Declare @TS_TBLID_REPORT_USAGE Int = 246 ;
    Declare @TS_TBLID_REPORT_CHANGEACTIONS Int = 247 ;
    Declare @TS_TBLID_REPORT_CHANGES Int = 248 ;
    Declare @TS_TBLID_EXTERNAL_USERS Int = 249 ;
    Declare @TS_TBLID_EXTERNAL_USER_RULES Int = 250 ;
    Declare @TS_TBLID_FOLDERSHARES Int = 251 ;

    -- Solution Framework tables.
    -- The entire 300 series of tableids are reserved for the Solution Framework
    -- These SHOULD NOT be used for platform table ids EJR

    Declare @TS_TBLID_SOLUTIONFRAMEWORK_001 Int = 300 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_002 Int = 301 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_003 Int = 302 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_004 Int = 303 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_005 Int = 304 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_006 Int = 305 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_007 Int = 306 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_008 Int = 307 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_009 Int = 308 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_010 Int = 309 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_011 Int = 310 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_012 Int = 311 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_013 Int = 312 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_014 Int = 313 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_015 Int = 314 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_016 Int = 315 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_017 Int = 316 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_018 Int = 317 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_019 Int = 318 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_020 Int = 319 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_021 Int = 320 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_022 Int = 321 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_023 Int = 322 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_024 Int = 323 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_025 Int = 324 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_026 Int = 325 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_027 Int = 326 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_028 Int = 327 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_029 Int = 328 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_030 Int = 329 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_031 Int = 330 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_032 Int = 331 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_033 Int = 332 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_034 Int = 333 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_035 Int = 334 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_036 Int = 335 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_037 Int = 336 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_038 Int = 337 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_039 Int = 338 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_040 Int = 339 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_041 Int = 340 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_042 Int = 341 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_043 Int = 342 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_044 Int = 343 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_045 Int = 344 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_046 Int = 345 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_047 Int = 346 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_048 Int = 347 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_049 Int = 348 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_050 Int = 349 ;
    -- Table Id values between 349 and 398 are reserved for Solution Framework tables.
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_099 Int = 398 ;
    Declare @TS_TBLID_SOLUTIONFRAMEWORK_100 Int = 399 ;




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

    -- 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] ,

    -- Value-to-text mapping also from tsDEF.h
    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(64)) As [Name on record changed]


    -- TODO
    -- Add another column to compose (if possible) the "value" that was changed in the selected table.
    -- This would require a CASE on TS_TABLEID and lookups of the Name / Title / Value from each.
    -- .... a lot of work ... Mayby only do it for "important" tables or where the displayed result
    -- would make sense add clarity ...

    From
    ts_AdminChanges admchg
    Where
    -- For the last XX days
    (DateDiff(dd,DateAdd(ss,admchg.TS_TIMESTAMP,'1/1/1970'),GetUtcDate())) < @DAYS

    -- Ignoring these 3 tables eliminates about 90% of the "noise" in the output.
    -- These tables seem to be related to stuff end-users are doing -- not administrative changes.
    AND (admchg.TS_TABLEID Not In (@TS_TBLID_REPORT_USAGE,@TS_TBLID_FIELDORDERINGS,@TS_TBLID_USERFORMSTATES) )
    Order By
    admchg.TS_TIMESTAMP
    ;
    [/code]


    This idea has been previously submitted:

    https://www.serenacentral.com/community/ideas/sbm/capture-change-history-for-admin-changes

    Please add your comments and vote to this idea!
    David J. Easter Commented by David J. Easter October 31, 2018
    #1 Reviewer  -  

    This idea has been previously submitted:

    https://www.serenacentral.com/community/ideas/sbm/capture-change-history-for-admin-changes

    Please add your comments and vote to this idea!

    SBM has the system report Users Change History. Create and delete are very usable but modify gives all modifications. Also modifications a user does on his/her own account. For example a change password or change in permissions or membership is not visible. So this is not very useful for audit purpose.

    Otger Cobben Commented by Otger Cobben October 31, 2018
    Top 50 Reviewer  -  

    SBM has the system report Users Change History. Create and delete are very usable but modify gives all modifications. Also modifications a user does on his/her own account. For example a change password or change in permissions or membership is not visible. So this is not very useful for audit purpose.

    Some extra information:
    We have security event monitoring in place which reads events from a logfile, sql table or windows event log. Several scenario's are configured based on these events which will fire an incident. For example:
    Account created and deleted within 24h
    Account added and removed from group within 24h
    User created and logon within 5 minutes
    User created or activated and logged from same IP
    Activated user locked or disabled more than 30 days
    Multiple failed logon from the same user in 2 minutes
    Multiple failed logon from the same IP in 5 minutes
    Multiple failed logon from the same IP in 24 hours
    Multiple failed logon from many IP on the same account in 5 minutes
    Multiple failed logon from many IP on the same account in 24 hours
    User changed more than 2 times within 24h
    Manual user management action outside busines shours

    Some events we can take from the licenceserver log. But there is no way to get user administration events. A solution can be that SBM generates an audit log where all user/group/rights administration activity is logged. It can also be logged in the windows event log.
    Otger Cobben Commented by Otger Cobben October 19, 2018
    Top 50 Reviewer  -  

    Some extra information:
    We have security event monitoring in place which reads events from a logfile, sql table or windows event log. Several scenario's are configured based on these events which will fire an incident. For example:
    Account created and deleted within 24h
    Account added and removed from group within 24h
    User created and logon within 5 minutes
    User created or activated and logged from same IP
    Activated user locked or disabled more than 30 days
    Multiple failed logon from the same user in 2 minutes
    Multiple failed logon from the same IP in 5 minutes
    Multiple failed logon from the same IP in 24 hours
    Multiple failed logon from many IP on the same account in 5 minutes
    Multiple failed logon from many IP on the same account in 24 hours
    User changed more than 2 times within 24h
    Manual user management action outside busines shours

    Some events we can take from the licenceserver log. But there is no way to get user administration events. A solution can be that SBM generates an audit log where all user/group/rights administration activity is logged. It can also be logged in the windows event log.

     

Print