Is it possible to join tables using AQL?

[CommunityUser]

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?

[Chitra_Lal]

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

[CommunityUser]

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.

[Chitra_Lal]

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 

[CommunityUser]

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.

[Chitra_Lal]

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.

 

Can you please double check and verify. 

Thank You,

Chitra

[CommunityUser]

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)`?

[Chitra_Lal]

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

[CommunityUser]

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.

[Chitra_Lal]

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 

[CommunityUser]

No, I do not have an EUM license. I was asking for Analytics tables in general.

How should we structure a search to select from multiple tables? Do they need to share the exact same rows, or just a subset?

[Chitra_Lal]

Hi Kyzhou,

As you don't have an EUM license hence this 401 error you see. You need to have EUM license to use browser_records and synth data. 

Now regarding your original query we can not select data to be shown from multiple tables unless they are from the same schema. Iff two tables that you are trying to see are in the same schema can you structure a query to fetch data combining both, but not in any other case. 

Hope that clarifies. Do let me know in case you have further queries. 

Thank You,

Chitra