For why you'd use lookup tables and how to upload them, see Lookup tables.
Basic query syntax
Let's say you've named your table storeNames. This query will select all data from that table:
FROM lookup(storeNames)
SELECT*
This query will select some specific attributes from that same table:
FROM lookup(storeNames)
SELECT store_ID, store_name, description
Query examples
The primary benefit of lookup tables is that you can use queries that combine that data with your New Relic-stored telemetry data.
Here are some query examples:
This query avoids hardcoding a long list of hosts by querying host names contained in a lookup table:
FROM Log
SELECTcount(*)
WHERE hostname IN(FROM lookup(myHosts)SELECT uniques(myHost))
Using JOIN queries can make your data more understandable. For example, this query for a custom event type uses the storeNames table to show the store names along with the total sales. Also notice this allows a limit of 10,000: that's because lookup tables support a higher limit than other NRQL data types.
FROM StoreEvent
JOIN(FROM lookup(storeNames)SELECT store_ID as storeId, storeName AS name LIMIT10000)ON shopId = storeId
SELECT shopId, storeName, totalSales
Here's a query that translates status codes to readable summaries of the status:
This query shows how several NRQL features can work together to get business information from queries of log data. The query extracts information about items from log messages, and uses JOIN to get user-friendly item names and to generate a table of item names and the number of items stored.
WITH aparse(message,'POST to carts: * body: {"itemId":"*","unitPrice":*}%')AS(URL, ItemID, Price)