How do I transpose a trellis label into a code for using in a drilldown

[waleeper]

I have a trellis view where I break down my charts into Cities. The labels are something like 'Charlotte, NC'. I can make a drilldown to my details page using the form.city=$trellis.value$.

The problem is now I want to improve the performance on my target page. It currently is pulling data for all 100 of my cities then filtering by the city name using a lookup table to convert 'Chartlotte, NC' to 'clt' which I can then apply to a hostname filter.

   index=data sourcetype=searchdata "string" 
    | eval fields=split(host, "."), market=mvindex(fields, 1)
    | lookup sitemapping sitecode as market OUTPUT region, sitecity, sitecode 
    | search sitecity="Charlotte, NC"
    | ...

What I would like to do is use tag::host="clt" so that I can filter the records in the initial search.

One option is to extract the code somehow from the Trellis, the other is to convert from the label to the code in my query before I do the search part.

I tried putting an inputlookup before the search, but that ends up filtering out all the data due to the results of the inputlookup.

| inputlookup market-mapping | search sitecity="Charlotte, NC" | fields sitecode
|  search index=data sourcetype=searchdata "string" tag::host=sitecode

The inputlookup by itself returns 'clt' in the example. Running the search by itself returns my data

Thanks

[efavreau · Accepted solution]

@waleeper You're real close. Your inputlookup needs to be in a search on its own, that results in exactly the key/value pair needed for the main search. I don't have your lookup file, so I fake one and then bring it to the key/value pair you need to start the real search. Here's the run anywhere example that results in tag::host=sitecode, which you're looking for when Charlotte is entered:

| noop 
    | stats count 
    | eval raw=split("sitecity=Charlotte sitecode=clt ; sitecity=NewYork sitecode=nyc ; sitecity=Rochester sitecode=roc",";") 
    | mvexpand raw 
    | rename raw as _raw 
    | extract auto=t 
    | search sitecity="Charlotte" 
    | eval "tag::host"=sitecode 
    | table "tag::host"

To use this in brackets in your search, it might look like this:

[| inputlookup market-mapping
       | search sitecity="Charlotte, NC"
        | eval "tag::host"=sitecode 
        | table "tag::host"] index=data sourcetype=searchdata "string"

What's happening here, is the search in the brackets is resolving first. When it runs, the search resolves to:

tag::host=clt index=data sourcetype=searchdata "string"

[efavreau]

This reads like a data problem, not a trellis problem. Host has the data you need, and you need to parse that (with a rex command likely), so you can filter from there.

[waleeper]

The problem is that if I parse it out with a Rex AFTER I pull in the 2.5M records it takes 10s, if I can use a tag it take 1.5s.

I can certainly parse it, this is fundamentally a performance issue of not being able to put what I pass from the drilldown into the initial search criteria.

I could put a tag friendly label on the dashboard, but management is going to complain about it saying NewYork-NY or Rochester-MN instead of 'New York, NY'.