DX Application Performance Management

Expand all | Collapse all

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

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

    Posted 03-15-2016 07:57 AM

    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



  • 2.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 03-17-2016 04:23 PM

    Hi!

     

    I see several questions here.

     

    Anyway, this should work for you

     

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

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

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

     

    introscope.agent.sqlagent.normalizer.regex.key1.pattern=(SELECT.*)

    introscope.agent.sqlagent.normalizer.regex.key1.replaceFormat=SELECT

     

    should send "SELECT[....]" to just "SELECT"

     

     

     

    If you want to refine this, you can use as many keys as you need.

     

    Regards,

    Roger



  • 3.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 07:32 AM

    Hi Roger,

     

    have you tried your suggestion please?

     

    i tried it (copy&paste) on 2 test apps, but it didnt work for me..

     

    thank you,

    stefan

     

    sql.png



  • 4.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 03-17-2016 04:38 PM

    Sorry, I think I misunderstood your question

     

    Anyway,

     

    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):

     

    Can be solved by using:

     

    introscope.agent.sqlagent.normalizer.regex.key1.replaceAll=true



  • 5.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 07:34 AM

    Hi Roger,

     

    yes, it can be the reason why it replaces just the first occur, however, the whole normalization doesnt work yet.

     

    thank you,

    stefan



  • 6.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 07:35 AM

    Hi all:

      Are there any questions outstanding or has this question been answered?

     

    Thanks

    Hal German



  • 7.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 08:01 AM

    Hi Hal,

     

    yes, the basic question is not answered yet.

     

    why the sql normalization doesnt replace all the specific SQL commands (like SELECT * FROM XY WHERE ZW .. ) and put it into 1 general group (SELECT) ? and how to do it?

     

    thank you,

    stefan



  • 8.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 08:25 AM

    Thanks Stefan

    DavidLewis hikodavis SergioMorales Lynn_Williams  do you want to weigh in on this?

     

    Thanks in advance for all the answers

     

    Hal



  • 9.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-05-2016 09:57 AM

    Hi guys

     

    this worked for me:

     

    introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer

     

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

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

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

    introscope.agent.sqlagent.normalizer.regex.key1.pattern=(SELECT.*)

    introscope.agent.sqlagent.normalizer.regex.key1.replaceFormat=SELECT

     

     

     

    thus this is a hot config, I had to restart my jboss (I think the query was cached).

     

    check the snapshot:

    SQLSnapshot.png

     

     

    Regards,

    Roger



  • 10.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-06-2016 03:01 AM

    Hi Roger,

     

    after adding "introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer" (that's obvious and i had it there before, but commented it out by accident), it partly works, however in weird way.

     

    1. it displays 2 groups with the same name (i.e. 2 SELECT groups and 2 CREATE groups) instead of 1 for Tomcat. the difference between the 2 groups are that in the first group (SELECT1) i have the real amount of responses per interval (i.e.16) and in the second one (SELECT2) just 1 response per interval.

    2. in IIS, it created a new general group (i.e SELECT) and added some of the unique SQL commands under it, but it still displays some unique SQL commands under their own and unique folder.

     

    both servers were restarted several times.

     

    and here is my configuration for Tomcat:

    introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer

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

    introscope.agent.sqlagent.normalizer.regex.keys=key1,key2

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

    introscope.agent.sqlagent.normalizer.regex.key1.pattern=(INSERT.*)

    introscope.agent.sqlagent.normalizer.regex.key1.replaceFormat=INSERT

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

    introscope.agent.sqlagent.normalizer.regex.key2.pattern=(CREATE.*)

    introscope.agent.sqlagent.normalizer.regex.key2.replaceFormat=CREATE

     

    and for IIS:

    introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer

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

    introscope.agent.sqlagent.normalizer.regex.keys=key1,key2,key3

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

    introscope.agent.sqlagent.normalizer.regex.key1.pattern=(SELECT.*)

    introscope.agent.sqlagent.normalizer.regex.key1.replaceFormat=SELECT

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

    introscope.agent.sqlagent.normalizer.regex.key2.pattern=(DELETE.*)

    introscope.agent.sqlagent.normalizer.regex.key2.replaceFormat=DELETE

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

    introscope.agent.sqlagent.normalizer.regex.key3.pattern=(UPDATE.*)

    introscope.agent.sqlagent.normalizer.regex.key3.replaceFormat=UPDATTE

     

    i have just some test app implemented in the IIS to test the RegexSqlNormalizer before doing it on the customer site and i dont know, maybe the app is written in some weird way and that's the reason why some of the SQL commands are put under the general replace format and the rest not, however in tomcat, it's no specific app generating the seen SQL commands, but the tomcat start process and therefore i suppose, i should have the same output as you have in jboss.

     

    sql-normalization.png

     

    thank you,

    stefan



  • 11.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-06-2016 09:41 AM

    Hi Stefan,

     

    looking at the duplicate of the issue #1 ("SELECT" metric) I inmediatly recall situation of duplicate metric I had when having different types in the values of an EPAgent metric. Maybe you can try retrieving both metrics using CLW for the first moments of them (7:45) and check the type of metric, and if there is any difference between them (like a blank space at the ending or something).

     

    About 2), are all those unique queries still retrieving info or is there just one response at the beginning of the agent working? Have you reset iis after applying the change? Again, documentation says its hot ... but is worth trying an iisreset. Is there any other sql configuration in that profile?

     

     

    Regards,

    Roger



  • 12.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-07-2016 02:28 AM

    Hi Roger,

     

    thank you for your tips!

     

    1) i noticed that there is really a difference between the 2 group of general SQL groups. =  a space is in the between the word ie. _SELECT,_INSERT (where _ means space). so the whole metric path is

    *SuperDomain*|app01|Tomcat|Tomcat Agent|Backends|mem%datasource (Hypersonic)|SQL|Dynamic|Update|INSERT

    and

    *SuperDomain*|app01|Tomcat|Tomcat Agent|Backends|mem%datasource (Hypersonic)|SQL|Dynamic|Update| INSERT

     

    do you have some idea why it is like this and how to fix it??

     

    2)

    both servers were restarted several times, so the IIS as well.

    there is no other sql configuration in that profile.

    and all those SELECT unique queries display info continuously, not just once at the beginning. however, the DELETE and UPDATE queries are fine = they have correctly just 1 groupfolder containing all the unique DELETE and UPDATE queries.

    any idea/tip here as well?

     

     

    sql-normalization2.png

     

    thank you,

    stefan



  • 13.  Re: RegexSqlNormalizer to display all unique SELECT requests under 1 general SELECT metric

    Posted 04-07-2016 08:03 AM

    Hi, Stefan

     

    For 1) I guess you can use a regex like \sselect.

     

    For 2 I'd ask support. It seems specific For    .net agents. Maybe you can also get a few metrics using clw to look At them closely.

     

    Regards,

    Roger

     

     

     

     

     

    El 7/4/2016, a las 3:26, pivst01 <communityadmin@communities-mail.ca.com<mailto:communityadmin@communities-mail.ca.com>> escribió:

     

    CA Communities <https://communities.ca.com/?et=watches.email.thread>

     

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

     

    reply from Stefan Pivoda<https://communities.ca.com/people/pivst01?et=watches.email.thread> in CA APM - View the full discussion<https://communities.ca.com/message/241874261?et=watches.email.thread#comment-241874261>