How to pull information of Change History section from SSM Database?

Answer

Description : Script required to pull information of Change History section from SSM Database

The change history details gets stored in 'FIELD_TRACKING' table.

By using the below query the change history details can be retrieved :

select * from field_tracking

**where clause can be added to the "table_name" column to pull records of a certain attribute/ business object

For Instance if we need to track the details of password reset for user. The below query will be helpful for us to determine just how many times the admin users have changed the passwords of their suppliers in the past.

Query:
select ft.FIELD_TRACKING_ID?
ft.created_by?
au_1.display_name as created_by_name
ft.CREATION_DATE?
ft.DESCRIPTION?
ft.FIELD_NAME?
ft.FIELD_NEW_VALUE?
ft.FIELD_OLD_VALUE?
ft.MODIFICATION_DATE?
ft.TABLE_ID?
ft.TABLE_NAME?
ft.TIMESTAMP?
ft.TRACKING_CATEGORY?
ft.TRACKING_TYPE?
ft.USER_ID
au_2.display_name as user_name
?from field_tracking ft
?application_user au_1
? application_user au_2
?where ft.field_name = 'Password'
?and au_1.au_id = ft.created_by
?and au_2.au_id = ft.user_id

This query can be executed by the system admin user on the application itself:

- Access the VSM application administration Tab.

- Expand the Tables tab on the left panel.

- Click on Field_Tracking

- On the right panel click on Data Tab and copy the above query and click on Go.

This will display the result in the application itself which can also be exported.