We are trying to pull SLA report for URL. But, we are getting the wrong data. The report shows the number 0 where we are expecting a percentage.
corrupt/duplicate data for these targets in the S_QOS_DATA_TABLE
Release : 9.0.2
Component : UIM - UMP_SLAREPORTS
The S_QOS_DATA table stores links for the reports to look for the QOS data, in this case: 'QOS_URL_RESPONSE'.
We found that for the URLs that were showing bad data in the report, there were duplicate row.
The duplicate rows had an incorrect origin value.
To correct the issue for the remaining URLs, we can take these steps.
1- Have your DBA backup the S_QOS_DATA table before proceeding.
2- Identify a target string from your XLS list that had bad data in the report.
3- Use UMP - SLM - Tools - SQL Query to run this query:
SELECT * FROM S_QOS_DATA WHERE TARGET LIKE 'name of bad URL';
4- Look at the "origin" column and compare this to the robot address for the probe in the IM console.
Part of the robot address will show the hub it reports to, for example: /domain/dec_rhub2/robothostname
In this example, we know that the correct "origin" value is 'dec_rhub2'
5- Run a select statement to validate before deleting the bad row(s).
SELECT * FROM S_QOS_DATA WHERE probe LIKE 'url_response' AND target LIKE 'name of bad URL' AND origin NOT LIKE 'dec_rhub2';
This should return 1 or more rows for the URLs where the SLA report is not working.
6- Delete the duplicate rows, change the statement above to a DELETE.
DELETE FROM S_QOS_DATA WHERE probe LIKE 'url_response' AND target LIKE 'name of bad URL' AND origin NOT LIKE 'dec_rhub2';
7- Refresh your report to see if the issue is fixed for that URL.
The url_response probe robots originally came from a different hub, the new origin/hub created a new duplicate row.
This broke this particular SLA report when it found more than 1 row.