Business iQ (Analytics)

cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to join tables using AQL?

Builder

Is it possible to join tables using AQL?

I have a table of IDs to Names:

id: name

1: user1

2: user2

3: user3

 

and another table of more data:

id: edu : ......

1 : bachelor : ....

2 : master : ....

3 : phd : ....

 

Is it possible to use AQL to join these two together so I can display the name corresponding to an id in a search?

Is it possible to join tables using AQL?
11 REPLIES 11
Highlighted
AppDynamics Team

Re: Is it possible to join tables using AQL?

Hi Kyzhou,

 

If you want to use the JOIN schema thing then you are hitting a limitation; JOIN schema is still not available in ADQL, so as of now its not possible to join two tables using ADQL. It's definitely what engg. team would like to add but it will require a design and architectural change and it's still in discussions, so nothing definitive on this.

 

However in a little deviation from the above use case, if your requirement is just to pull over metrics from two different tables(like few from browser request, few from synth), we still have little scope(but this might be limited again at some level). But what I would suggest here is that go ahead and try it out and mostly this should work.

 

A quick example would be like this :

SELECT browserRecords.pagename, browserRecords.metrics.`End User Response Time (ms)` FROM synth_session_records WHERE browserRecords.pageurl = "/abc"

 

Hope this helps.

 

Thank You,

Chitra



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.
Builder

Re: Is it possible to join tables using AQL?

Hello Chitra,

 

Could you explain a little further on how to select from two different tables? I tried on my existing two tables a query:

 

SELECT nameTable.id, nameTable.names FROM dataTable WHERE nameTable.id = "1"

 

but was given an error: Unrecognized field [nameTable.names] not found in schema

 

Also, I could not query the synth_session_records, I was given a 401 unauthorized.

AppDynamics Team

Re: Is it possible to join tables using AQL?

Hi Kyzhou,

 

This 401 unauthorized error means you are not correctly connected to your ES. Also, are you getting 401 error only for this specific table, synth_session_records and other ADQLs are working fine for you? I doubt if Analytics is at usable for you?

 

Can you please share a screenshot of where you are trying this.

 

Thanks,

Chitra 

 



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.
Builder

Re: Is it possible to join tables using AQL?

Analytics is usable for me.. queries to my custom schema work as expected.

 

This only occurs for the two tables involved in the query example that was provided above.

AppDynamics Team

Re: Is it possible to join tables using AQL?

Hi Kyzhou,

 

This query should work fine,provided you have sessions data and the URL you are specifying is correct and there are no syntax mistakes in your query(like any leading or trailing spaces etc).

 

SELECT browserRecords.pagename, browserRecords.metrics.`End User Response Time (ms)` FROM synth_session_records WHERE browserRecords.pageurl = "/abc"

 

Refer to the below screenhsot from my environment for this similar query.

Screen Shot 2018-04-12 at 4.01.26 PM.png 

Can you please double check and verify. 

 

Thank You,

Chitra



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.
Builder

Re: Is it possible to join tables using AQL?

I am still told that the query is unauthorized.

 

Perhaps the query you have given does not work for on-premise Controllers?

 

I copied and pasted the query you provided and tried for the pageurl:

<domain>:8090

<domain>:8090/controller

<domain>:9080

 

But none return any results.

 

In general, what criteria do you need to satisfy to be able to select from multiple tables? For example, does synth_session_records also have matching fields for pagename and metrics.`End User Response Time (ms)`?

 

AppDynamics Team

Re: Is it possible to join tables using AQL?

Hi kyzhou,

 

Can you please clarify what tables are these and where have you created these tables? This query(the one I gave with synthetic records * browser records) works for on prem controllers fine. Can you please confirm that if you remove all teh filters at least then it works for you first?What result you get for the below queries.

 

SELECT * FROM synth_session_records

SELECT * FROM browser_records

SELECT * FROM transactions

 

 

If however these are not analytics tables you can not use ADQL for querying them. If you get expected result for the queries above we would request that you open an Appdynamics help ticket so that we can get on a webex and check this further.

 

Thanks,

Chitra



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.
Builder

Re: Is it possible to join tables using AQL?

The tables I wish to select from are custom event schema created through the Analytics API.

 

Both synth_session_records and browser_records give the same error: Failed to receive query results [HTTP 401 Unauthorized].

 

I can only select results from transactions.

AppDynamics Team

Re: Is it possible to join tables using AQL?

Hi kyzhou,

 

Do you have an EUM license and actually have EUM & synth data being monitored? If yes, please raise a helpdesk ticket with a screenshot of your license screen->EUM /Analytics section so that we can troubleshoot why this error. 

 

Thanks,

Chitra 



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.