AnsweredAssumed Answered

RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

Question asked by pivst01 Employee on Mar 15, 2016
Latest reply on Apr 7, 2016 by rogelio.dipasquale

Hi All,

 

i tried to create just 1 metric group for all SELECT SQL requests with RegexSqlNormalizer, but I couldnt get the wanted result.

 

i tried several pattern&replaceFormat, but couldnt get the proper way to display all the SELECT SQL resusests under 1 general SELECT metric in Backend/app_name/SQL/Dynamic/Query.

just some of the metrics were displayed under the general SELECT metrics and the rest had it's own unique SELECT metric.

 

i also noticed that if I apply the next pattern&replaceFormat, then just the 1st match of "id" is replaced by "MY_STRING" match and the rest is ignored (=left in original format):

 

introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=id

introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=MY_STRING

 

from the original SELECT:

SELECT OU_ID, OU_PARENT_ID, OU_NAME, OU_UPDATED_ON, OU_CREATED_ON, OU_DESCRIPTION, OU_GUID, OU_TYPE, OU_STATUS, OU_LDAP_PATH, OU_LDAP_USER, OU_LDAP_PASS, OU_DOMAIN_NAME1, OU_DOMAIN_NAME2, OU_DELETED FROM NS4_ORG_UNIT WHERE OU_STATUS = ? AND OU_DELETED = ? AND (OU_DOMAIN_NAME1 IS NOT NULL OR OU_DOMAIN_NAME2 IS NOT NULL)

it makes ->

SELECT ou_MY_STRING, ou_parent_id, ou_name, ou_updated_on, ou_created_on, ou_description, ou_guid, ou_type, ou_status, ou_ldap_path, ou_ldap_user, ou_ldap_pass, ou_domain_name1, ou_domain_name2,

ou_deleted FROM ns4_org_unit WHERE ou_status = 2 AND ou_deleted = 0 AND (ou_domain_name1 IS NOT NULL OR ou_domain_name2 IS NOT NULL)

 

 

 

 

these SELECTs are displayed correctly under the general SELECT metric:

 

SELECT * FROM NS4_ARCHIVE_DB WHERE ADB_DELETED = ? ORDER BY ADB_NAME ASC

SELECT * FROM NS4_ZONE WHERE ZONE_DELETED = ? ORDER BY ZONE_NAME ASC

SELECT REG_VALUE FROM NS4_REGISTRY WHERE REG_USER = ? AND REG_SECTION = ? AND REG_KEY = ?

SELECT UIG_GRP_ID FROM NS4_USER_IN_GROUP WHERE UIG_USR_ID = ? ORDER BY UIG_GRP_ID ASC

 

BUT i.e this doesnt .

SELECT OU_ID, OU_PARENT_ID, OU_NAME, OU_UPDATED_ON, OU_CREATED_ON, OU_DESCRIPTION, OU_GUID, OU_TYPE, OU_STATUS, OU_LDAP_PATH, OU_LDAP_USER, OU_LDAP_PASS, OU_DOMAIN_NAME1, OU_DOMAIN_NAME2, OU_DELETED FROM NS4_ORG_UNIT WHERE OU_STATUS = ? AND OU_DELETED = ? AND (OU_DOMAIN_NAME1 IS NOT NULL OR OU_DOMAIN_NAME2 IS NOT NULL)

->

 

and if i use the next pattern&replaceFormat:

introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer

introscope.agent.sqlagent.normalizer.regex.matchFallThrough=false

introscope.agent.sqlagent.normalizer.regex.key_tables.replaceAll=false

introscope.agent.sqlagent.normalizer.regex.key_tables.caseSensitive=false

introscope.agent.sqlagent.normalizer.regex.keys=key_tables

#introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=(SELECT|DELETE|INSERT|UPDATE|CREATE|DROP|SET|SHUTDOWN).*

#introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=$1

#introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=(SELECT|DELETE|INSERT|UPDATE|CREATE|DROP|SET|SHUTDOWN).*

#introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=SQL

#introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=.*(SELECT|DELETE|INSERT|UPDATE).*

#introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=Cumulated $1  
introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=.*

introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=SQL

#etc..

 

and  makes from

SELECT OU_ID, OU_PARENT_ID, OU_NAME, OU_UPDATED_ON, OU_CREATED_ON, OU_DESCRIPTION, OU_GUID, OU_TYPE, OU_STATUS, OU_LDAP_PATH, OU_LDAP_USER, OU_LDAP_PASS, OU_DOMAIN_NAME1, OU_DOMAIN_NAME2, OU_DELETED FROM NS4_ORG_UNIT WHERE OU_STATUS = ? AND OU_DELETED = ? AND (OU_DOMAIN_NAME1 IS NOT NULL OR OU_DOMAIN_NAME2 IS NOT NULL)

->

SQL ou_parent_id, ou_name, ou_updated_on, ou_created_on, ou_description, ou_guid, ou_type, ou_status, ou_ldap_path, ou_ldap_user, ou_ldap_pass, ou_domain_name1, ou_domain_name2, ou_deleted FROM ns4_org_unit WHERE ou_status = 2 AND ou_deleted = 0 AND (ou_domain_name1 IS NOT NULL OR ou_domain_name2 IS NOT NULL)

 

i gt the same result with hundreds others SQL request.

 

any idea why it is like this and how can i fix it?

 

thank you,

stefan

Outcomes