Query on each row of a sub-SELECT

How do I do the following?

I have a list of values from a SELECT, e.g. “SELECT val FROM Logs WHERE condition=‘predicate’”. I want to add a table to a dashboard consisting of, for each value in the list, the rows matching that value. Something like this pseudo-SQL:

DECLARE $var VARCHAR(255)
DECLARE $cursor CURSOR FOR SELECT field1 FROM Logs WHERE condition='predicate';

OPEN $cursor;
FETCH NEXT FROM $cursor INTO $var;

WHILE !FINISHED($cursor) {
    $var = next($cursor)
   TO_DASHBOARD('MyDashboard', SELECT field1, field2, field3, field4 FROM Log WHERE field4 LIKE *$var*
}

Hi, @christopher.peters: I don’t think that is possible in NRQL. What you can do is put a table on the dashboard listing the values for field1, and link it to another table listing all of the detail items. When the user selects an item from the field1 list, it will filter the detail list to show only the matching items:

1 Like

You can actually filter the existing dashboard now from a widget. You have to toggle the option when configuring a widget. You can have two tables. One where you facet and then filter and the other where you want to get the results of the filter. One would think that you could filter the table you just selected, but it doesn’t. You have to have at least a second widget.

Yea, no sub selects is a real challenge sometimes. It forces you to get creative and sometimes that ends up with something that isn’t super intuitive. Filter() can get you down the path many times, but complex things no so much…

1 Like

@christopher.peters Looks like you got some good tips and tricks here. Were you able to figure this query out?

Not entirely, I’m afraid. I’ve been able to do some facet filtering, and that’s very useful, but what I want is this:

I’m ingesting several different HTTP log files into New Relic. Let’s say I have table “log1” with fields “path”, “trace_id”, and “status”, and table “log2” with fields “path”, “trace_id”, and “status”. In my dashboard, I can create a table of all trace IDs with status 500. I’d like to be able to click on a trace ID in the “500s” table and filter the contents of “log2” by that trace ID.

I was able to jury-rig this functionality somewhat by creating a table with the query: “SELECT count(*) FROM Log FACET trace_id, status WHERE trace_id is not null AND status >= 500 LIMIT MAX”, and that’s OK, but the count conveys no useful information and I’d really rather not have it in there.

SELECT count(*) FROM Log facet event.amzn_trace_id, event.sport where event.amzn_trace_id is not null and event.status = '500' limit max

Make a dashboard. On the first page create this widget table.

SELECT lastest(event.sport), count(*) FROM Log where event.amzn_trace_id is not null and event.status = '500' facet event.amzn_trace_id limit max

Add a second tab to the dashboard and add this table.

Select * from Log2 since 7 days ago limit max

Go back to the first page and edit the widget to filter the second widget tab.

Save all that. Refresh your dashboard view in the browser and view the first page and the table. Hover your cursor over a trace id on the left and click on it. This should link you to the tab and filter its table using the trace id. You can then sort the view etc.

2 Likes

@christopher.peters Did this further information from @6MM help get you further along with what you were looking for?

Do you mean latest where you had lastest?

Yes, it was just a typo.

Functionally, that’s almost entirely equivalent to what I have now. Guess I have to have some kind of aggregate function in there to filter dashboards. :frowning:

What’s missing now? Just curious. The tab approach makes it interactive. Our folks like having this kind of dashboard ‘tool’.

“Tab approach”? I’m not sure what you mean. And to answer your question, it’s not that there’s anything missing - I can click on a trace ID and pull up log entries for same. It just annoys me to see the count in there, because (as trace IDs are unique to a request) it’s not a particularly useful piece of information.

Tab approach means having a dashboard that has tabs or pages. One page shows high level info. One or more other pages or tabs for “drill down”. Did you make a dashboard tab?

The count is helpful in my opinion. Same as in the distributed tracing app. If you have 3 spans the cyclomatic complexity of the business transaction is lower than one that has 500 spans (in theory). I’m making a lot of guesses about what you have there since I cannot see it. If event.amzn_trace_id is your parent trace id and you have a number of events that have this ID and you group them and count them you can then sort on the count and then link to the other tab for a trace with a high number of events…

I made a second dashboard, not a dashboard tab.

Works the same. When there are tabs its more contained I guess.

1 Like

But now that you’ve mentioned it, I’m setting up my test dashboard with tabs :slight_smile: