Use lookups to query table data

This query avoids hardcoding a long list of hosts by querying host names contained in a lookup table:

FROM Log
SELECT count(*)
WHERE hostname IN (FROM lookup(myHosts) SELECT uniques(myHost))

Using JOIN queries can make your data easier to understand. For example, this query for a custom event type uses the storeNames table to show the store names along with the total sales.

FROM StoreEvent
JOIN (FROM lookup(storeNames) SELECT store_ID as storeId, storeName AS name LIMIT 10000) ON shopId = storeId
SELECT shopId, storeName, totalSales

This allows a limit of 10,000 because lookup tables support a higher limit than other NRQL data types, as is mentioned in the query limits section.

This query maps shopId values to a broader storeCategory value by using a JOIN clause on lookup table data. This enables you to perform aggregations on StoreEvent records based on these broader categories and extract new insights from your data.

FROM StoreEvent
JOIN (FROM lookup(storeCategories) SELECT store_ID as storeId, storeCategory) ON shopId = storeId
SELECT sum(totalSales) FACET storeCategory

Here's a query that translates status codes to readable summaries of the status:

FROM Transaction
  JOIN (FROM lookup(statusCodeTable) SELECT status_code, status_summary, status_definition)
  ON http.statusCode=status_code
SELECT count(*) FACET status_summary

Here are some example results:

This query shows how several NRQL features can work together to get business information from queries of log data. The below query:

1. Extracts information about items from log messages using aparse()

2. Uses JOIN on lookup table data to get user-friendly item names

3. Outputs a table of item names and the number of items stored

   WITH aparse(message, 'POST to carts: * body: {"itemId":"*","unitPrice":*}%') AS (URL, ItemID, Price)
   FROM Log
     JOIN (FROM lookup(itemNames) SELECT ItemID, itemName) ON ItemID
   SELECT count(*) FACET itemName
   WHERE message LIKE 'POST to carts%'
   SINCE 30 days ago

   Copy

   Here are some example results:

   

This query combines lookups and GeoIP to find which locations have the most unsuccessful statuses:

FROM Log
  JOIN (FROM lookup(statusCodeTable) SELECT status_code, status_summary, status_definition)
  ON CacheResponseStatus=status_code
SELECT count(*) WHERE ClientIP.countryName IS NOT NULL AND status_summary != 'Success'
FACET ClientIP.countryName, status_summary, CacheResponseStatus
SINCE 1 day ago LIMIT MAX

Here are some example results: