Purging Analytics Data

Data purging is an option to remove historical data in WSO2 API Manager Analytics. This is important since it is not possible to delete tables or table data in WSO2 API Manager Analytics. By purging data, you can achieve high performance in data analysis without removing analyzed summary data. This data is contained in the following tables:

ORG_WSO2_APIMGT_STATISTICS_WORKFLOW
ORG_WSO2_APIMGT_STATISTICS_PERMINUTEREQUEST
ORG_WSO2_APIMGT_STATISTICS_PERHOURREQUEST
ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST
ORG_WSO2_APIMGT_STATISTICS_PERMINUTERESPONSE
ORG_WSO2_APIMGT_STATISTICS_PERHOURRESPONSE
ORG_WSO2_APIMGT_STATISTICS_PERDAYRESPONSE
ORG_WSO2_APIMGT_STATISTICS_PERMINUTEEXECUTIONTIMES
ORG_WSO2_APIMGT_STATISTICS_PERHOUREXECUTIONTIMES
ORG_WSO2_APIMGT_STATISTICS_PERDAYEXECUTIONTIMES
ORG_WSO2_APIMGT_STATISTICS_THROTTLE
ORG_WSO2_APIMGT_STATISTICS_FAULT
ORG_WSO2_CARBON_IDENTITY_OAUTH_TOKEN_ISSUANCE
LOGANALYZER

Make sure you do not purge data in tables other than those mentioned above and the tables related to alerts, because data purging deletes your summarized historical data.

You can perform data purging in API Manager Analytics using either one of the following methods.

Purging data for a specific table

Follow the instructions below to purge data of a selected table via the Management Console.

Sign in to the WSO2 API-M Analytics Management Console.
https://<APIM-ANALYTICS_HOST>:<APIM-ANALYTICS_PORT>/carbon/
In the Main tab, click Data Explorer to open the Data Explorer page.
Select the required table in the Table Name field, and click Schedule Data Purging as shown below.
The Schedule Data Purging option is displayed only for users who have a role that has the record delete permission enabled. The latter permission can be Delete permission under Record enabled. For instructions on setting this permission to users, see WSO2 DAS-Specific User Permissions.
Click here to see where the record delete permission is found in the permission tree..
Sign in to the WSO2 API-M Analytics Management Console.
https://<APIM-ANALYTICS_HOST>:<APIM-ANALYTICS_PORT>/carbon/
Click Configure and thereafter click List which is under the User and Roles tab.

Click Roles.
Click Permissions to see the permissions that are assigned to specific user roles.
The API-M Analytics related user permissions are highlighted in the following screen shot together with the record delete permission.

As WSO2 API-M Analytics is based on WSO2 DAS 3.2.0, for more information on WSO2 API-M Analytics related user permissions, see WSO2 DAS-Specific User Permissions in the WSO2 Data Analytics Server documentation.
This opens the Schedule Data Purging dialog box.

In the Schedule Data Purging dialog box, set the time and days within which you want to purge data as shown below. Then click Save.

If you are purging the statistics related tables, do not purge data that is less than 2 days old, because it can result in a data loss. However, the latter mentioned criteria is not applicable when working with alert related tables.

Field	Description
Enable Data Purging	Whether you want to enable data purging or not.
Schedule Time (Either cron string or HH:MM)	Enter the time at which you want to purge data via a cron expression or by defining the time in the following format: HH:MM. For example, the following cron expression will configure the data purging job to run at 12:00 PM (noon) every day : `0 0 12 * * ?` For more information on cron expressions, go to Oracle Documentation.
Purge Record Older Than (Days)	Define the value that determines as to how long you will be storing data in a table (i.e., keep data that corresponds to the last 'n' number of days back in the selected table. For example, if you give 1 as the value, the system will purge all data stored before yesterday.

Repeat these steps individually for all the tables mentioned above, so that you can ensure that all the data from the respective tables are cleared regularly.

Removing scheduled data purging operations

Follow the instructions below to remove a data purging operation that you have already scheduled.

Sign in to the WSO2 API-M Analytics Management Console as an admin user if you have not already signed in.
https://<APIM-ANALYTICS_HOST>:<APIM-ANALYTICS_PORT>/carbon/
Click Main, and then click Data Explorer.
Select the required table in the Table Name field, and then click Schedule Data Purging. The Schedule Data Purging dialog box appears.
Clear the Enable Data Purging check box as shown below.
Click Save, and close the dialog box.

Purging data globally

When you purge data using the global data purging operation, it will affect all the tenants.

Open the <API-M_ANALYTICS_HOME>/repository/conf/analytics/analytics-config.xml file.

Change the contents under the <analytics-data-purging> property as shown below:

<analytics-data-purging>
    <purging-enable>true</purging-enable>
    <cron-expression>0 0 12 * * ?</cron-expression>
    <purge-include-tables>
         <table>ORG_WSO2_APIMGT_STATISTICS_WORKFLOW</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERMINUTEREQUEST</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERHOURREQUEST</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERMINUTERESPONSE</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERHOURRESPONSE</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERDAYRESPONSE</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERMINUTEEXECUTIONTIMES</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERHOUREXECUTIONTIMES</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_PERDAYEXECUTIONTIMES</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_THROTTLE</table>
    	 <table>ORG_WSO2_APIMGT_STATISTICS_FAULT</table>
    	 <table>LOGANALYZER</table>
    	 <table>ORG_WSO2_CARBON_IDENTITY_OAUTH_TOKEN_ISSUANCE</table>
      </purge-include-tables>
    <data-retention-days>2</data-retention-days>
  </analytics-data-purging>

Change the data-retention-days based on your requirement. Note that if you are purging the statistics related tables, do not purge data that is less than 2 days old, because it can result in a data loss. However, the latter mentioned criteria is not applicable when working with alert related tables.

The properties of the above configuration file are shown below.

Property	Description
<purging-enable>	Change the value to true if you want to enable data purging.
<cron-expression>	The cron expression to define how you want to schedule the data purging operation. For example, the following cron expression will configure the archive job to run at 12:00 PM (noon) every day : `0 0 12 * * ?` For more information on cron expressions, go to Oracle Documentation.
<purge-include-tables>	Specify the tables of which you want to purge data. By default, it is configured to perform data purging on all tables as follows: `<table>.*</table>` However, you can specify the required tables by defining a regular expression or a table name within the `<table>` property. Define one tag for each regular expression if you want to specify multiple tables.
<data-retention-days>	Define the value in order to retain data of only the last 'n' number of days in the selected table. For example, 365, which is the default value, will purge all data stored before a year.

Save your changes.

Disabling data purging in a clustered mode

In a clustered mode you can disable the scheduled data purging tasks being operated in a particular node (e.g., a node which is used for database tasks) using a start up parameter. Start the API Analytics of that particular node by executing the following command to disable data purging:

sh <API-M_ANALYTICS_HOME>/bin/wso2server.sh -DdisableDataPurging=true

Otherwise, you can permanently use the start up parameter as a system property by adding the following line of code to the <API-M_ANALYTICS_HOME>/bin/wso2server.sh file.

-DdisableDataPurging=true \

Sample Code Snippet

while [ "$status" = "$START_EXIT_STATUS" ]
do
    $JAVACMD \
    -Xbootclasspath/a:"$CARBON_XBOOTCLASSPATH" \
    $JVM_MEM_OPTS \
    -XX:+HeapDumpOnOutOfMemoryError \
    -XX:HeapDumpPath="$CARBON_HOME/repository/logs/heap-dump.hprof" \
    $JAVA_OPTS \
    -Dcom.sun.management.jmxremote \
    -classpath "$CARBON_CLASSPATH" \
    -Djava.endorsed.dirs="$JAVA_ENDORSED_DIRS" \
    -Djava.io.tmpdir="$CARBON_HOME/tmp" \
    -Dcatalina.base="$CARBON_HOME/lib/tomcat" \
    -Dwso2.server.standalone=true \
    -Dcarbon.registry.root=/ \
    -Djava.command="$JAVACMD" \
    -Dcarbon.home="$CARBON_HOME" \
    -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager \
    -Dcarbon.config.dir.path="$CARBON_HOME/repository/conf" \
    -Djava.util.logging.config.file="$CARBON_HOME/repository/conf/etc/logging-bridge.properties" \
    -Dcomponents.repo="$CARBON_HOME/repository/components/plugins" \
    -Dconf.location="$CARBON_HOME/repository/conf"\
    -Dcom.atomikos.icatch.file="$CARBON_HOME/lib/transactions.properties" \
    -Dcom.atomikos.icatch.hide_init_file_path=true \
    -Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false \
    -Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true \
    -Dcom.sun.jndi.ldap.connect.pool.authentication=simple  \
    -Dcom.sun.jndi.ldap.connect.pool.timeout=3000  \
    -Dorg.terracotta.quartz.skipUpdateCheck=true \
    -Djava.security.egd=file:/dev/./urandom \
    -Dfile.encoding=UTF8 \
    -Djava.net.preferIPv4Stack=true \
    -Dcom.ibm.cacheLocalHost=true \
    -DworkerNode=false \
	-DdisableDataPurging=true \
    -Dorg.apache.cxf.io.CachedOutputStream.Threshold=104857600 \
    $NODE_PARAMS \
    org.wso2.carbon.bootstrap.Bootstrap $*
    status=$?

Purging alert related data

Details of all the alerts that are generated by WSO2 API Manager Analytics can be viewed via the WSO2 API-M Analytics Dashboard, because all information related alerts are persisted in the WSO2 API-M Analytics related database. However, if you do not wish to maintain the WSO2 API-M Analytics alert related data that is older than a given time period, you can purge the following tables based on a data retention period of your choice that is no less than 2 days; however, note that this will delete summarized historical data related to WSO2 API-M Analytics alerts and you will not be able to view the deteted data via the WSO2 API-M Analytics Dashboard. The latter mentioned alert related data is contained in the following tables:

ORG_WSO2_ANALYTICS_APIM_RESPONSEPERMINPERAPISTREAM
ORG_WSO2_ANALYTICS_APIM_IPACCESSSUMMARY
ORG_WSO2_ANALYTICS_APIM_REQUESTCOUNTTABLE
ORG_WSO2_ANALYTICS_APIM_REQUESTPERMINPERAPISTREAM
ORG_WSO2_ANALYTICS_APIM_MARKOVMODELTABLE
ORG_WSO2_ANALYTICS_APIM_ALLAPIMALERTSSTREAM
ORG_WSO2_ANALYTICS_APIM_ALERT_UNUSUALIPACCESS
ORG_WSO2_ANALYTICS_APIM_REQUESTPERMINSTREAM
ORG_WSO2_ANALYTICS_APIM_REQUESTPATTERNCHANGEDSTREAM
ORG_WSO2_APIMGT_STATISTICS_FAULT
ORG_WSO2_ANALYTICS_APIM_APIHEALTHMONITORALERTSTREAM
ORG_WSO2_ANALYTICS_APIM_IPACCESSALERTCOUNT
ORG_WSO2_ANALYTICS_APIM_REQUESTPATTERNALERTSUMMARYTABLE
ORG_WSO2_ANALYTICS_APIM_APIAVAILABILITY
ORG_WSO2_ANALYTICS_APIM_ALERT_ABNORMALTOKENREFRESH
ORG_WSO2_ANALYTICS_APIM_REQUESTPERAPIPERCENTILE
ORG_WSO2_ANALYTICS_APIM_TIERLIMITHITTINGALERT
ORG_WSO2_APIMGT_STATISTICS_THROTTLE
ORG_WSO2_ANALYTICS_APIM_ABNORMALRESPONSETIMEALERTSTREAM
ORG_WSO2_ANALYTICS_APIM_ACCESSTOKENREFRESHTIMEDIFFERENCE
ORG_WSO2_ANALYTICS_APIM_RESPONSEPERAPIPERCENTILE
ORG_WSO2_ANALYTICS_APIM_ALERT_ABNORMALTIERUSAGEALERT
ORG_WSO2_ANALYTICS_APIM_REQUESTPERCENTILE
ORG_WSO2_ANALYTICS_APIM_ABNORMALREQUESTSPERMINALERTSTREAM
ORG_WSO2_ANALYTICS_APIM_RESPONSETIMEPERAPIPERCENTILE
ORG_WSO2_APIMGT_STATISTICS_WORKFLOW
ORG_WSO2_ANALYTICS_APIM_RESPONSEPERCENTILE
ORG_WSO2_ANALYTICS_APIM_ABNORMALBACKENDTIMEALERTSTREAM