DX Application Performance Management

Expand all | Collapse all

APMSQL Grafana with APM 10.5.1

Jump to Best Answer
  • 1.  APMSQL Grafana with APM 10.5.1

    Posted 10-16-2018 04:45 AM

    Hello,

    in the APM 10.7.0 documentation "Using Grafana with CA APMSQL" I find

    Before you start, check that you have the following components set up:

    APM 10.5+ (APMSQL must be available)

    This is a nice to know (we are still using APM 10.5.1),

    but there is no additional information how to install/use the new APMSQL with an old APM 10.5.1.

     

    I configure the new APM 10.7.0 SP2 APMSQL to point to an old APM 10.5.1 Manager, the connection test work well and I got some data from the manager also.

    http://manager.intern:9080/grafanarest/

    {"message":"Connection established successfully"}

     

    But all the time Grafana send a request to the APMSQL I got a bad request error only.

    "TEIID30504 ws-source: TEIID15005 Error Calling HTTP Service - 400 Bad Request"

     

    I find a SQL Exception in the IntroscopeEnterpriseManager.log.

    com.wily.introscope.appmap.em.sql.SQLException: Unable to parse query: [line 1:82 mismatched input 'where' expecting {<EOF>, EXCEPT, FOR, INTERSECT, OPTION, ORDER, UNION, ';'}]

     

    My question,

    how to use Grafana with an old APM 10.5.1 or is this a documentation typo.

     

    With regards,

    Lutz

     

    p.s.

    See https://docops.ca.com/ca-apm/10-7/en/integrating/apmsql-cookbook/using-grafana-with-ca-apmsql 



  • 2.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-16-2018 09:17 AM

    Olina  WrenW  Can you help?



  • 3.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-16-2018 06:41 PM

    Hi Lutz,

    If you have not done so already it might be a good idea to test Squirrel with the "APM 10.7.0 SP2 APMSQL -> APM 10.5.1 EM" to verify if this is a grafana specific problem or more generic for the above combination.

    Are you using the grafanarest war file deployed OOTB with APMSQL or have you separately deployed it to a Tomcat 8+ instance?

     

    Thanks

     

    Lynn



  • 4.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 09:00 AM

    Hello,

    I use APMSQL (installed with the APM 10.7.0 installer and updated to SP2).

    After I configure "apmsql-server-jetty-config.xml" (the datasource used by the grafana restapi) to point to an APMSQL 10.5.1 the error messages in the IntroscopeEnterpriseManager.log disappears.

     

    In a first test I configure APMSQL 10.7.0 SP2 to point to an APM 10.5.1 EM and the datasource used by the grafana restapi point to the local APMSQL 10.7.0 SP2.

     

    But I get no data furthermore and some new eror messages.
    Sometimes I got "couldn't query timezone for metric data; leaving timestamps as UTC" and a NullPointerException.
    I use Grafana 5.2.4.

     

    Any idea,

    Lutz

     

    Appandage:

    couldn't query timezone for metric data; leaving timestamps as UTC
    Before result.from: 2018-10-17T12:00:32.419Z
    Before result.to: 2018-10-17T12:05:32.421Z
    Error has occured while processing request ->null
    java.lang.NullPointerException
            at java.util.GregorianCalendar.computeFields(GregorianCalendar.java:2353)
            at java.util.GregorianCalendar.computeFields(GregorianCalendar.java:2323)
            at java.util.Calendar.setTimeInMillis(Calendar.java:1815)
            at java.util.Calendar.setTime(Calendar.java:1781)
            at java.text.SimpleDateFormat.format(SimpleDateFormat.java:954)
            at java.text.SimpleDateFormat.format(SimpleDateFormat.java:947)
            at java.text.DateFormat.format(DateFormat.java:356)

     

    Appandage:

    Some log data, apmsqlgrafanaservice.log

    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:91 - Entering prepareResponse of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:96 - Exiting prepareResponse of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:146 - Exiting executeSqlAndGenerateResponse of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:51 - Entering /search API{"target":"apmsql 'APM.*' 'GC Heap.*Use' agg_value"}
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:101 - Entering extractSQLqueryFromRequest of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:110 - Exiting extractSQLqueryFromRequest of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:53 - Parsed query is:apmsql 'APM.*' 'GC Heap.*Use' agg_value
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:82 - Exiting /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:115 - Entering acquireAPMSQLConnection of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:128 - Exiting acquireAPMSQLConnection of /search API
    2018-10-17 14:16:20 INFO  APMSQLSearchMethod:133 - Entering executeSqlAndGenerateResponse of /search API
    2018-10-17 14:16:21 INFO  APMSQLSearchMethod:91 - Entering prepareResponse of /search API
    2018-10-17 14:16:21 INFO  APMSQLSearchMethod:96 - Exiting prepareResponse of /search API
    2018-10-17 14:16:21 INFO  APMSQLSearchMethod:146 - Exiting executeSqlAndGenerateResponse of /search API
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:719 - couldn't query timezone for metric data; leaving timestamps as UTC
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:719 - couldn't query timezone for metric data; leaving timestamps as UTC
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:606 - Before result.from: 2018-10-17T12:11:48.645Z
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:607 - Before result.to: 2018-10-17T12:16:48.646Z
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:606 - Before result.from: 2018-10-17T12:11:48.518Z
    2018-10-17 14:16:49 INFO  APMSQLQueryMethod:607 - Before result.to: 2018-10-17T12:16:48.518Z
    2018-10-17 14:16:49 ERROR APMSQLQueryMethod:196 - Error has occured while processing request ->null
    2018-10-17 14:16:49 ERROR APMSQLQueryMethod:196 - Error has occured while processing request ->null
    2018-10-17 14:17:14 INFO  APMSQLQueryMethod:719 - couldn't query timezone for metric data; leaving timestamps as UTC
    2018-10-17 14:17:14 INFO  APMSQLQueryMethod:606 - Before result.from: 2018-10-17T12:12:14.166Z
    2018-10-17 14:17:14 INFO  APMSQLQueryMethod:607 - Before result.to: 2018-10-17T12:17:14.167Z
    2018-10-17 14:17:14 ERROR APMSQLQueryMethod:196 - Error has occured while processing request ->null
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:51 - Entering /search API{"target":""}
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:101 - Entering extractSQLqueryFromRequest of /search API
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:110 - Exiting extractSQLqueryFromRequest of /search API
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:53 - Parsed query is:
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:82 - Exiting /search API
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:115 - Entering acquireAPMSQLConnection of /search API
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:128 - Exiting acquireAPMSQLConnection of /search API
    2018-10-17 14:17:17 INFO  APMSQLSearchMethod:133 - Entering executeSqlAndGenerateResponse of /search API



  • 5.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 09:58 AM

    Hello,

    sometimes I find select like the following in the apmsqlserverout.log, see lelow.

    I got some data for the select in Squirrel.

    Lutz

     

    Appandage:

    I test the select (see below) with Squirrel and got some data.

     

    Before result.from: 2018-10-17T13:38:07.402Z
    Before result.to: 2018-10-17T13:43:07.402Z
    kSmartStorTimeZone: sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]
    After result.from: 2018-10-17 15:38:07
    After result.to: 2018-10-17 15:43:07
    diffHours<1
    Target has more than 1:false
    rawCommand: apmsql 'APM.WebView' 'GC Heap|Bytes In Use' agg_value

    select metric_path,year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) as theTime,

     (timestampdiff(SQL_TSI_SECOND,  FROM_UNIXTIME(0), PARSETIMESTAMP( year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts)  ,'yyyy-MM-dd hh:mm:ss')))*1000 as tstamp,
    sum(value_count) as value_count,
    avg(agg_value) as agg_value,
    min(min_value) as min_value,
    max(max_value) as max_value
    from numerical_metric_data
    where frequency=15000
    and ts between '2018-10-17 15:34:00' and '2018-10-17 15:39:00'
    and agent_name like_regex 'APM.WebView'
    and metric_path like_regex 'GC Heap|Bytes In Use'
    group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by metric_path
    limit 2147483647



  • 6.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 10:10 AM

    Dear Lutz:

    I was hoping that APM Development would respond. On the documentation part, please open a new case to see if it is a doc bug.

     

    On the getting to work, that should be branched into a new thread . 



  • 7.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 12:02 PM

    Adding to Lynn's response, when APMSQL first became available, we had customers who explained that when the EMs are clustered and connected to LDAP/MS ADS, it requires that the Collectors also be configured to connect the same for authentication. I'm not sure if this has been corrected with the release version, but please try and see if adding your MOM realms.xml to your collectors fixes the issue after restarting them.



  • 8.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 04:12 PM

    Hmm,

    the EMs in the Cluster use the same realms.xml configuration and I can logon to all EMs via the Workstation.

    The used user to logon is defined in the LDAP only.

    The user used by the grafana restapi is defind in the EM in the users.xml, and I use the same file in all EMs in a Cluster.

    Thanks,

    Lutz

     

    p.s.

    Suggested by Hallett German,

    I open a ticket to get some more information, to request a documantation update.



  • 9.  Re: APMSQL Grafana with APM 10.5.1

    Posted 10-17-2018 04:16 PM

    Since you have configured realms to use LDAP, then you should use a user from your domain. See if that works. Otherwise, disable LDAP config and use local file only.



  • 10.  Re: APMSQL Grafana with APM 10.5.1
    Best Answer

    Posted 10-18-2018 06:36 AM

    Hello,

    good news, it seems to work now.

     

    I connect the APMSQL Grafana 10.7.0 SP2 application to one of the small APM 10.5.1 Cluster and the Grafana restapi is able to request some data. But sometimes it is to slow and only "Datapoints outside time range" will displayed.

     

    Thanks for any help,

    Lutz

     

    BTW

    The Grafana application can't handle large systems, I think.

    I got JSON errors like this

    "[{ "error":"org.teiid.jdbc.TeiidSQLException: Unable to transform the column value 4294967296 to a Integer."}][ { "target":"GC Heap:Bytes In Use",

     

    I open a ticket to report the problem.



  • 11.  Re: APMSQL Grafana with APM 10.5.1

    Posted 12-16-2018 01:37 PM

    Hello,

    the problem with large integer values will fixed with grafanarest-10.7.0.135.war.

    The fix will be included in 10.7 SP3 (and later).

    With regards,

    Lutz