DX Application Performance Management

Expand all | Collapse all

No data points (grafana+apmsql)

  • 1.  No data points (grafana+apmsql)

    Posted 10-04-2018 11:00 AM

    Hello guys,
    I have a question here in my environment that I am not being able to evolve, I would like to know if anyone has already gone through this or how can I do to evolve in this matter.
    the situation is as follows:
    I did the installation according to the manual, I installed the datasource and everything worked perfect.
    - When I query for example a metric from MOM, I get the desired result.
    apmsql '. * | * *' 'Enterprise Manager | Health: Heap Capacity (%)' agg_value exact

     

    When I try to query some of the agents I have on this server I only get "No data point" and "Query Inspector", it does not show any useful information.
    The metric I want to capture is that of the image below.
    any ideas?


  • 2.  Re: No data points (grafana+apmsql)

    Posted 10-04-2018 06:02 PM

    Dear Community:
    Does someone have suggestions or next steps for Felix?

     

    Thanks

    Hal German



  • 3.  Re: No data points (grafana+apmsql)

    Posted 10-04-2018 08:37 PM

    Hi Felix,

    I was wondering if you had a problem with your agent specifier or metric specifier regex for the "SCCD-Server01" agent, but on the face of it all looks OK:

    Using Grafana with CA APMSQL - CA Application Performance Management - 10.7 - CA Technologies Documentation 

    Is the problem only occurring for some agents?

     

    Thanks,

     

    Lynn



  • 4.  Re: No data points (grafana+apmsql)

    Posted 10-05-2018 08:41 AM

    Hello Lynn,

    I can not actually capture the data of any agent. Only from MOM and the collectors ...



  • 5.  Re: No data points (grafana+apmsql)

    Posted 10-10-2018 10:27 AM

    Can you show us your query?



  • 6.  Re: No data points (grafana+apmsql)

    Posted 10-10-2018 10:59 AM

    Query:

    apmsql '.*|.*|SCCD-Server01' 'CPU:Processor Count' agg_value exact

     

    Query Inspector:

    {
      "data": "",
      "status": 200,
      "statusText": "OK",
      "xhrStatus": "complete",
      "request": {
        "method": "POST",
        "url": "api/datasources/proxy/3/query",
        "data": {
          "timezone": "browser",
          "panelId": 2,
          "dashboardId": 7,
          "range": {
            "from": "2018-10-10T14:52:57.167Z",
            "to": "2018-10-10T14:57:57.167Z",
            "raw": {
              "from": "now-5m",
              "to": "now"
            }
          },
          "rangeRaw": {
            "from": "now-5m",
            "to": "now"
          },
          "interval": "500ms",
          "intervalMs": 500,
          "targets": [
            {
              "target": "apmsql '.*|.*|SCCD-Server01' 'CPU:Processor Count' agg_value exact",
              "refId": "B",
              "hide": false,
              "type": "timeserie"
            }
          ],
          "maxDataPoints": 688,
          "scopedVars": {
            "__interval": {
              "text": "500ms",
              "value": "500ms"
            },
            "__interval_ms": {
              "text": "500",
              "value": "500"
            }
          },
          "adhocFilters": []
        }
      }
    }



  • 7.  Re: No data points (grafana+apmsql)

    Posted 10-12-2018 02:20 AM

    Hi Felix,

    What are you seeing for the query in the apmsqlgrafanaservice.log under your EM_HOME/APMSqlServer/logs directory?

    I am also still trying to get Grafana to work with 10.7 SP2 - so far I am not getting any data points for any queries including the EM  

     

    Thanks,

     

    Lynn



  • 8.  Re: No data points (grafana+apmsql)

    Posted 10-15-2018 08:53 AM

    Lynn,
    I'm looking at this log, but it does not have any new query data being fed there ... until I tried to log it as a debug to see if something appeared, but I can not see anything in the log...

     

     

    [root@spcdsrvv4502 logs]# tail -f apmsqlgrafanaservice.log
    2018-10-11 10:52:24 INFO  MLog:80 - MLog clients using log4j logging.
    2018-10-11 10:52:24 INFO  C3P0Registry:204 - Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
    2018-10-11 10:52:24 WARN  ActiveManagementCoordinator:56 - A C3P0Registry mbean is already registered. This probably means that an application using c3p0 was undeployed, but not all PooledDataSources were closed prior to undeployment. This may lead to resource leaks over time. Please take care to close all PooledDataSources.
    2018-10-11 10:52:24 INFO  AbstractPoolBackedDataSource:462 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2t2lf89yrkvewkrx09pk|6b57696f, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.teiid.jdbc.TeiidDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2t2lf89yrkvewkrx09pk|6b57696f, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:teiid:apm_base@mm://0.0.0.0:54323, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 600, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
    2018-10-11 10:52:25 INFO  GrafanaRestApplication:43 - APMSQL Connection established successfully!!
    2018-10-11 11:16:36 INFO  MLog:80 - MLog clients using log4j logging.
    2018-10-11 11:16:36 INFO  C3P0Registry:204 - Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
    2018-10-11 11:16:36 WARN  ActiveManagementCoordinator:56 - A C3P0Registry mbean is already registered. This probably means that an application using c3p0 was undeployed, but not all PooledDataSources were closed prior to undeployment. This may lead to resource leaks over time. Please take care to close all PooledDataSources.
    2018-10-11 11:16:37 INFO  AbstractPoolBackedDataSource:462 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2t2lf89yrlqjdx1oci4qm|6b57696f, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.teiid.jdbc.TeiidDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2t2lf89yrlqjdx1oci4qm|6b57696f, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:teiid:apm_base@mm://0.0.0.0:54323, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 600, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
    2018-10-11 11:16:37 INFO  GrafanaRestApplication:43 - APMSQL Connection established successfully!!

     

    see, today is day 15-10 and the last log I have in this file is from 11-10 ...



  • 9.  Re: No data points (grafana+apmsql)

    Posted 10-15-2018 03:58 PM

    Lynn,

    see, doing some tests today I managed to generate some logs when I make the query, the log below is when I run one of those queries that comes with no data.

    PS: I am using the tomcat integration to make this communication.

     

    my grafana query: apmsql '.*|.*|.*' 'Enterprise Manager|Connections:Number of Workstations' agg_value exact

    Log Result:

    10/15/18 04:50:49.501 PM BRT [DEBUG] [NIO8] [TRANSPORT] processing message:MessageHolder: key=45 contents=Invoke interface org.teiid.client.security.ILogon.logon
    10/15/18 04:50:49.501 PM BRT [DEBUG] [NIO8] [AUDIT_LOG]  [apm_base, null 418bed20-8182-4c2e-8379-d4db6f2d199a USERPASSWORD spcdsrvv4502.spcddom0001.serpro 10.***.***.*** 00505685F40E false] <session.logon-request>
    10/15/18 04:50:49.501 PM BRT [DEBUG] [NIO8] [SECURITY] authenticateUser 418bed20-8182-4c2e-8379-d4db6f2d199a JDBC
    10/15/18 04:50:49.543 PM BRT [DEBUG] [NIO8] [SECURITY] Logon successful, created session: sessionid=vkTu3vjwS9kY; userName=418bed20-8182-4c2e-8379-d4db6f2d199a@teiid-security; vdbName=apm_base; vdbVersion=1; createdTime=Mon Oct 15 16:50:49 BRT 2018; applicationName=JDBC; clientHostName=spcdsrvv4502.spcddom0001.serpro; clientHardwareAddress=00505685F40E; IPAddress=10.***.***.***; securityDomain=teiid-security; lastPingTime=Mon Oct 15 16:50:49 BRT 2018
    10/15/18 04:50:49.543 PM BRT [DEBUG] [NIO8] [AUDIT_LOG]  [418bed20-8182-4c2e-8379-d4db6f2d199a@teiid-security] <session.logon-success>
    10/15/18 04:50:49.543 PM BRT [DEBUG] [NIO8] [TRANSPORT] send message: MessageHolder: key=45 contents=org.teiid.client.security.LogonResult@6265ad3f
    10/15/18 04:50:49.547 PM BRT [DEBUG] [NIO8] [TRANSPORT] processing message:MessageHolder: key=46 contents=Invoke interface org.teiid.client.security.ILogon.ping
    10/15/18 04:50:49.547 PM BRT [DEBUG] [NIO8] [SECURITY] Keep-alive ping received for session: vkTu3vjwS9kY
    10/15/18 04:50:49.547 PM BRT [DEBUG] [NIO8] [TRANSPORT] send message: MessageHolder: key=46 contents=null
    10/15/18 04:50:49.549 PM BRT [DEBUG] [NIO8] [TRANSPORT] processing message:MessageHolder: key=47 contents=Invoke interface org.teiid.client.DQP.executeRequest
    10/15/18 04:50:49.549 PM BRT [INFO] [NIO8] [COMMAND_LOG]     START USER COMMAND:    startTime=2018-10-15 16:50:49.549    requestID=vkTu3vjwS9kY.0    txID=nullsessionID=vkTu3vjwS9kY    applicationName=JDBC    principal=418bed20-8182-4c2e-8379-d4db6f2d199a@teiid-security    vdbName=apm_base    vdbVersion=1    sql=select
    ts
    from numerical_metric_data
    where frequency=15000
        and agent_name like '%Custom Metric Agent%'
        and metric_path like '%Enterprise Manager%GC Heap:GC Duration%'
        and ts between Timestampadd(sql_tsi_second, -20, Now()) AND Now()
    order by ts asc, ts asc
    10/15/18 04:50:49.549 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] Request Thread vkTu3vjwS9kY.0 with state NEW
    10/15/18 04:50:49.550 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] vkTu3vjwS9kY.0 Command has no cache hint and result set cache mode is not on.
    10/15/18 04:50:49.550 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] vkTu3vjwS9kY.0 executing  select
    ts
    from numerical_metric_data
    where frequency=15000
        and agent_name like '%Custom Metric Agent%'
        and metric_path like '%Enterprise Manager%GC Heap:GC Duration%'
        and ts between Timestampadd(sql_tsi_second, -20, Now()) AND Now()
    order by ts asc, ts asc
    10/15/18 04:50:49.552 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] ProcessTree for vkTu3vjwS9kY.0 SortNode(0) output=[wsdb.ts] [SORT] [wsdb.ts]
      AccessNode(1) output=[wsdb.ts] SELECT wsmodel.metric_data.ts FROM wsmodel.metric_data WHERE (wsmodel.metric_data.frequency = 15000) AND (wsmodel.metric_data.agent_name LIKE '%Custom Metric Agent%') AND (wsmodel.metric_data.metric_path LIKE '%Enterprise Manager%GC Heap:GC Duration%') AND (wsmodel.metric_data.ts >= Timestampadd(sql_tsi_second, -20, Now())) AND (wsmodel.metric_data.ts <= Now())

    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [BUFFER_MGR] Creating TupleBuffer: 64 [public.numerical_metric_data.ts] [class java.sql.Timestamp] batch size 2048 of type FINAL
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [CONNECTOR] vkTu3vjwS9kY.0.1.9 Create State
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [BUFFER_MGR] Creating TupleBuffer: 65 [wsdb.ts] [class java.sql.Timestamp] batch size 2048 of type PROCESSOR
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] Created intermediate sort buffer 65
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [BUFFER_MGR] vkTu3vjwS9kY.0.1.9 Blocking on source query vkTu3vjwS9kY.0.1.9
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [BUFFER_MGR] vkTu3vjwS9kY.0 Blocking on source request(s).
    10/15/18 04:50:49.553 PM BRT [DEBUG] [Worker5_QueryProcessorQueue29] [PROCESSOR] Request Thread vkTu3vjwS9kY.0 - processor blocked
    10/15/18 04:50:49.554 PM BRT [DEBUG] [Worker6_QueryProcessorQueue30] [PROCESSOR] Running task for parent thread Worker5_QueryProcessorQueue29
    10/15/18 04:50:49.554 PM BRT [DEBUG] [Worker6_QueryProcessorQueue30] [CONNECTOR] vkTu3vjwS9kY.0.1.9 Processing NEW request: SELECT wsmodel.metric_data.ts FROM wsmodel.metric_data WHERE (wsmodel.metric_data.frequency = 15000) AND (wsmodel.metric_data.agent_name LIKE '%Custom Metric Agent%') AND (wsmodel.metric_data.metric_path LIKE '%Enterprise Manager%GC Heap:GC Duration%') AND (wsmodel.metric_data.ts >= {ts'2018-10-15 16:50:29.553'}) AND (wsmodel.metric_data.ts <= {ts'2018-10-15 16:50:49.553'})
    10/15/18 04:50:49.555 PM BRT [DEBUG] [Worker6_QueryProcessorQueue30] [CONNECTOR] vkTu3vjwS9kY.0.1.9 Obtained execution
    10/15/18 04:50:49.555 PM BRT [DEBUG] [Worker6_QueryProcessorQueue30] [COMMAND_LOG]     START DATA SRC COMMAND:    startTime=2018-10-15 16:50:49.555    requestID=vkTu3vjwS9kY.0    sourceCommandID=1    executionID=9    txID=null    modelName=wsmodel    translatorName=apm-translator    sessionID=vkTu3vjwS9kY    principal=418bed20-8182-4c2e-8379-d4db6f2d199a@teiid-security    sql=SELECT wsmodel.metric_data.ts FROM wsmodel.metric_data WHERE (wsmodel.metric_data.frequency = 15000) AND (wsmodel.metric_data.agent_name LIKE '%Custom Metric Agent%') AND (wsmodel.metric_data.metric_path LIKE '%Enterprise Manager%GC Heap:GC Duration%') AND (wsmodel.metric_data.ts >= {ts'2018-10-15 16:50:29.553'}) AND (wsmodel.metric_data.ts <= {ts'2018-10-15 16:50:49.553'})

     

     



  • 10.  Re: No data points (grafana+apmsql)

    Posted 10-15-2018 11:07 PM

    Hi Felix,

    I also enabled the grafanarest service via Tomcat and eventually after taking the raw queries from the Tomcat apmsqlgrafanaservice.log and checking them via Squirrel I was able to get something to work in grafana.

    I have an agent named the same as you (SCCD-Server01) so I can also test your query which now works for me

    These queries are now working for me:

    apmsql '.*|.*|.*' 'Enterprise Manager|GC Heap:GC Duration' agg_value exact

    apmsql '.*|.*|SCCD-Server01' 'CPU:Processor Count' agg_value exact

    apmsql '.*|.*|.*' 'Enterprise Manager|Heap Capacity (%)' agg_value exact

     

    See screenshots below

     

    One other thing I did was to restart EM and APMSQLServer after setting unlimited connections for APM Data SQL rest api in my IntroscopeEnterpriseManager.properties because I was suspicious of problems in that area, so not sure if that helped:

    # If value of this property is set to positive number then
    # APM Data SQL rest api will be limited to number of paralel connections.
    # Otherwise transfer is unlimited.
    # Default value: 5
    #introscope.enterprisemanager.restapi.sql.connectionLimit=5
    introscope.enterprisemanager.restapi.sql.connectionLimit=0

    Using the new 10.5 APMSQL Server feature the MOM l - CA Knowledge 

     

    Hope that information helps

     

    Regards,

     

    Lynn



  • 11.  Re: No data points (grafana+apmsql)

    Posted 10-16-2018 02:00 AM

    Hi Felix,

    I restarted EM and APMSQLServer again after reverting to "introscope.enterprisemanager.restapi.sql.connectionLimit=5" and it did not cause any problems so that is probably not relevant.

    Maybe something else got resolved with my original EM and APMSQLServer restart?

     

    I did notice that after SP2 was installed on my Linux EM these lines were added to end of file APMSqlServer.properties with Ctrl-M characters probably due to Windows carriage return

    ===

    ^M
    # Directory on which APMSQL temporary buffer will be written, if it is empty or directory is invalid, it will redirect to temp directory^M
    # Eg. Windows : C://APM/buffer/ and Linux : /root/APM/buffer/ ^M
    com.ca.apm.sqlserver.buffer.directory=^M

    ===

    Anyway I removed them and set com.ca.apm.sqlserver.buffer.directory=/tmp/apmsqlserverbuffer

    Maybe all irrelevant but thought I would mention it.

     

    If your problems continue creating a support case is probably the best option so we can analyse the root cause in more detail.

    https://comm.support.ca.com/csupport/CaseManagement/cases/new 

     

    Thanks,

     

    Lynn



  • 12.  Re: No data points (grafana+apmsql)

    Posted 10-16-2018 03:59 PM

    lynn,

    made the changes that indicated, I reviewed all my configuration, however I can not see any metric other than mom (I even created a key in the team center as a system key) but nothing worked. with regard to the call, was my first choice, but I was asked to find the solution here in the community, because it would be easier to get help.



  • 13.  Re: No data points (grafana+apmsql)

    Posted 10-16-2018 09:30 PM

    Hi Felix,

    I have only tested on standalone EM.

    The fact that you only get MOM data could indicate some problem for accessing the Collectors via AppMap API used by APMSQL e.g. Does the APMSQL server support using introscope.ap - CA Knowledge 

    Is introscope.apm.feature.enabled=false on the Collectors?

     

    Thanks

     

    Lynn



  • 14.  Re: No data points (grafana+apmsql)

    Posted 10-17-2018 12:08 PM

    Lynn,

    Today the after sale that serves us was here and together with him we did an investigation of this issue and finally we managed to make it work in a certain way.
    I believe it is an APMSql bug, but what we were able to identify was:
    we used squirelsql to execute the queries directly and we could see the following:

    1. now it is bringing the information of the agent but the filters that we put seem to have no effect. See the image below:

     

    i run the apmsql command as follow:

    apmsql 'spcdsrvv0895|.*|.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact

     

    as you can see he is bringing the data from another frontend that I have that is the SAJ Mesa Cadastre. The problem is that this other backend belongs to another server that has no relation to the frontend I want to monitor ...

     

    see that in squirrel the result I get is the same as apmsql



  • 15.  Re: No data points (grafana+apmsql)

    Posted 10-17-2018 08:24 PM

    Hi Felix,

    Thanks for the information.

    I found similar behaviour using:

    apmsql 'agent_host|.*|.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact

    I thought my previous test with "apmsql '.*|.*|SCCD-Server01' 'CPU:Processor Count' agg_value exact " was working but that was because I only had 1 agent.

    The <agent specifier regex> for 'agent_host|agent_process|agent_name' seems to be not getting interpreted by APMSQL.

    I have tested trying to filter with any of 'agent_host|agent_process|agent_name' and none are working.

     

    If you change squirrel to return unlimited rows instead of 100 you will see 'Frontends|Apps|%:Responses Per Interval' data for all agents returned

    I also also added agent_host,agent_process,agent name into the squirrel query to confirm the data e.g.

    select agent_host,agent_process,agent_name,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-18 04:49:50' and '2018-10-18 05:19:50'
    and agent_name like_regex 'willy03-I2438|.*|.*'
    and metric_path like 'Frontends|Apps|%:Responses Per Interval'
    group by agent_host,agent_process,agent_name,metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by agent_host,agent_process,agent_name,metric_path
    limit 2147483647

     

    The Weblogic agent I have is under a different host to willy03-I2438

     

     

    As you say this is a generic APMSQL problem and not specific to Grafana

    Would you like to create a support case for it or shall I?

     

    Thanks

     

    Lynn



  • 16.  Re: No data points (grafana+apmsql)

    Posted 10-17-2018 09:26 PM

    Hi Felix,

    Having looked again at the query I have realised what the problem is

    We have been using traditional agent specifier regex 'agent_host|agent_process|agent_name'

    However the query via Grafana (apmsqlgrafanaservice.log) is built for agent_name only (agent_name like_regex ...)

    Therefore the agent specifier regex should actually be for agent_name only e.g. this works for me:

    apmsql '.*SCCD-Server01.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact

     

    After some tests in Squirrel for MOM and Collector agent_name with Custom Metric Agent (Virtual)* I found you need to escape the parentheses.

    However from apmsqlgrafanaservice.log I also found that Grafana strips out a single '\' so it needs a double escape '\\' e.g. these work:

    MOM: apmsql 'Custom Metric Agent \\(Virtual\\)' 'Enterprise Manager|Health:Heap Capacity (%)' agg_value exact
    Collector: apmsql 'Custom Metric Agent \\(Virtual\\) \\(willy03-I2438@25001\\)' 'Enterprise Manager|Health:Heap Capacity (%)' agg_value exact

     

    The examples in the docops show an agent_name type of syntax but it is not made clear that <agent specifier regex> is for agent_name only:

    Using Grafana with CA APMSQL - CA Application Performance Management - 10.7 - CA Technologies Documentation 

     

     

    I think that will cover your problems -  please let me know

     

    I will plan to request a docops update and also create a KB for APMSQL/Grafana queries

     

    Thanks

     

    Lynn



  • 17.  Re: No data points (grafana+apmsql)

    Posted 10-18-2018 01:59 AM


  • 18.  Re: No data points (grafana+apmsql)

    Posted 10-18-2018 01:12 PM

    lynn,
    unfortunately for me this solution still did not work, see in the examples below in both squirrel and grafana, none of the queries brings a satisfactory result.
    ps: on this server that I'm testing I have over 2000 agents, so I'm testing on several to see if I can get a response from any of them, but to no avail.

     

    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-18 11:39:23' and '2018-10-18 11:54:23'
    and agent_name like_regex '.*jboss\\_p\\_06857\\_eassinarfb.*'
    and metric_path like 'Frontends|Apps|%:Responses Per Interval'
    group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by metric_path

     

     

     

    Another empty result:

    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-18 11:39:23' and '2018-10-18 11:54:23'
    and agent_name like_regex '.*spcdsrvv0895.*'
    and metric_path like 'Frontends|Apps|%:Responses Per Interval'
    group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by metric_path

     

     

    logs from apmsqlgrafanaservice.log:

    Before result.from: 2018-10-18T14:39:23.811Z
    Before result.to: 2018-10-18T14:54:23.812Z
    kSmartStorTimeZone: sun.util.calendar.ZoneInfo[id="America/Sao_Paulo",offset=-10800000,dstSavings=3600000,useDaylight=true,transitions=129,lastRule=java.util.SimpleTimeZone[id=America/Sao_Paulo,offset=-10800000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=10,startDay=1,startDayOfWeek=1,startTime=0,startTimeMode=0,endMode=3,endMonth=1,endDay=15,endDayOfWeek=1,endTime=0,endTimeMode=0]]
    After result.from: 2018-10-18 11:39:23
    After result.to: 2018-10-18 11:54:23
    diffHours<1
    Target has more than 1:false
    rawCommand: apmsql '.*spcdsrvv0895.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact
    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-18 11:39:23' and '2018-10-18 11:54:23'
    and agent_name like_regex '.*spcdsrvv0895.*'
    and metric_path like 'Frontends|Apps|%:Responses Per Interval'
    group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by metric_path
    limit 2147483647
    Result Set is Empty for given query ->apmsql '.*spcdsrvv0895.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact and count is 0
    i is 0 hence printing [
    Empty String is :[
    {}]



  • 19.  Re: No data points (grafana+apmsql)

    Posted 10-18-2018 06:32 PM

    Hi Felix,

    For the 1st query:

    I changed my agent name to be "jboss_p_06857_eassinarfb" and ran some tests

    I used this Grafana query which successfully returned results:

    apmsql '.*jboss\\_p\\_06857\\_eassinarfb.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact 

     

    My apmsqlgrafanaservice.log shows one less '\' because the first '\' was just used as an escape character and that query also works in Squirrel. 

    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-19 03:23:49' and '2018-10-19 03:38:49'
    and agent_name like_regex '.*jboss\_p\_06857\_eassinarfb.*'
    and metric_path like 'Frontends|Apps|%:Responses Per Interval'
    group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
    order by metric_path
    limit 2147483647

     

    NOTE: Using the '\\' query in Squirel does not work:

     

    ALSO NOTE: You actually don't need to escape an underscore character because it is not a special regex character so this Grafana query also works:

    apmsql '.*jboss_p_06857_eassinarfb.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact

     

    For the 2nd query:

    apmsql '.*spcdsrvv0895.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact

    If spcdsrvv0895 is a hostname (agent_host) and not actually part of the agent_name then you won't get anything returned because you must only use the agent_name in the agent specifier regex.

    If you still have a problem please post a screenshot of the relevant agent node from the Metric Browser so I can also test with same agent_name.

     

    Thanks

     

    Lynn



  • 20.  Re: No data points (grafana+apmsql)

    Posted 10-19-2018 12:04 AM

    Hi Felix,

    I will also check with Engineering if the Grafana <agent specifier regex> being limited to agent_name is working as designed because it seems rather a limitation if you have multiple hostnames with same agent_name and you only want the retrieve agent data from a specific hostname.

    Regards,

    Lynn 



  • 21.  Re: No data points (grafana+apmsql)

    Posted 10-19-2018 07:45 AM

    Hello Felix, Lynn.

    I tried to use the APMSQL restapi with APM 10.5.1 (see APMSQL Grafana with APM 10.5.1) and Grafana 5.2.4, the first time I thought it doesn't work, because I got some error messages. But, after I use a small cluster with some hundred resourcen only, everything works well (sometimes).

    unfortunately for me this solution still did not work, see in the examples below in both squirrel and grafana, none of the queries brings a satisfactory result.
    ps: on this server that I'm testing I have over 2000 agents, so I'm testing on several to see if I can get a response from any of them, but to no avail.

    In general I get useful dashboards only, als long as I use dedicated agent namens (without a wildcard .*). On the other hand, the documantation said, the restapi handle only one agent but multible metrics (see Using Grafana with CA APMSQL - CA Application Performance Management - 10.7 - CA Technologies Documentation).

    agent specifier regex
    Matches a single agent using RegEx query.

    metrics specifier regex
    Matches a single agent (for time series queries), or multiple metrics (for table queries) using a regular expression.

    And I limit the time intervall to 5 minutes in Grafana.

     

    Unfortunately, several samples are not very useful.

    apmsql '.*thieves.*' 'CPU|Processor 0:Utilization.*' agg_value

    This is useful only, if only one agent name match.

    And the sample try to collect all "CPU" or "Processor 0:Utilization" metrics. But from the history perspective, the metric "CPU|Processor 0:Utilization.*" should be used (I think). In the past, a filter like "CPU\|Processor 0:Utilization.*" was used to do this. Unfortunately, the samples doesn't fit well and the restapi handle the filters in a different way.

     

    A comment only,

    Lutz



  • 22.  Re: No data points (grafana+apmsql)

    Posted 10-21-2018 05:27 PM

    Thanks Lutz Mader for your input.

    Once I get the confirmation from Engineering that the Grafana <agent specifier regex> being limited to agent_name is working as designed I will request a documentation update to add more clarity to the overall query syntax (probably based on the KB I have written so far: CA APM Grafana APMSQL queries - CA Knowledge)



  • 23.  Re: No data points (grafana+apmsql)

    Posted 10-25-2018 07:38 PM

    I did not yet receive any update from Engineering, so I am going to submit a request to ask our Tech Info team to update the documentation and confirm validity with Engineering.