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):
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 .
and if i use the next pattern&replaceFormat:
#introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=Cumulated $1 introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=.*
and makes from
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?
I see several questions here.
Anyway, this should work for you
should send "SELECT[....]" to just "SELECT"
If you want to refine this, you can use as many keys as you need.
have you tried your suggestion please?
i tried it (copy&paste) on 2 test apps, but it didnt work for me..
Sorry, I think I misunderstood your question
Can be solved by using:
yes, it can be the reason why it replaces just the first occur, however, the whole normalization doesnt work yet.
Are there any questions outstanding or has this question been answered?
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?
DavidLewis hikodavis SergioMorales Lynn_Williams do you want to weigh in on this?
Thanks in advance for all the answers
this worked for me:
thus this is a hot config, I had to restart my jboss (I think the query was cached).
check the snapshot:
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:
and for IIS:
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.
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?
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
*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??
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?
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.
El 7/4/2016, a las 3:26, pivst01 <firstname.lastname@example.org<mailto:email@example.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>