externaldata

You have an Axiom dataset that contains access logs with a field employeeID. You want to add extra information to your APL query by cross-referencing each employee ID in the Axiom dataset with an employee ID defined in an external lookup table. The lookup table is hosted somewhere else in CSV format.

External lookup table

employeeID, email, name, location
00001, tifa@acme.com, Tifa Lockhart, US
00002, barret@acme.com, Barret Wallace, Europe
00003, cid@acme.com, Cid Highwind, Europe

Query

let employees = externaldata (employeeID: string, email: string, name: string, location: string) ["http://example.com/lookup-table.csv"] with (format="csv", skipFirstRow=true);
accessLogs
| where severity == "high"
| lookup employees on employeeID
| project _time, severity, employeeID, email, name

Output

This example extends the original dataset with the fields email and name. These new fields come from the external lookup table.

Use a lookup table from an external source to extend an OTel logs dataset with a field that contains human-readable names for each service.

External lookup table

serviceName,humanreadableServiceName
frontend,Frontend
frontendproxy,Frontendproxy
flagd,Flagd
productcatalogservice,Productcatalog
loadgenerator,Loadgenerator
checkoutservice,Checkout
cartservice,Cart
recommendationservice,Recommendations
emailservice,Email
adservice,Ads
shippingservice,Shipping
quoteservice,Quote
currencyservice,Currency
paymentservice,Payment
frauddetectionservice,Frauddetection

Query

let LookupTable = externaldata (serviceName: string, humanreadableServiceName: string) ["http://example.com/lookup-table.csv"] with (format="csv", ignoreFirstRecord=true);
['otel-demo-traces']
| lookup kind=leftouter LookupTable on $left.['service.name'] == $right.serviceName
| project _time, span_id, ['service.name'], humanreadableServiceName

Output