topic Default values for CSV lookups that don't match? in Getting Data In

Default values for CSV lookups that don't match?

plucas_splunk — Tue, 28 Feb 2017 00:32:30 GMT

I have a vehicle fleet lookup table like:

vehicle_id,vehicle_year,vehicle_type,vehicle_ends,vehicle_agency,vehicle_livery
1,1912,"A",2,"San Francisco Municipal Railway","San Francisco Municipal Railway"
228,1934,"",2,"Blackpool Tramways","Blackpool Tramways"
578,1896,"G",2,"Market Street Railway","Market Street Railway"
737,1952,"PCC",1,"Brussels, Belgium","Zurich, Switzerland"
1807,1928,"Milan",1,"Milan, Italy","Milan, Italy (1928)"

A transforms.conf file like:

[Historic_fleet]
filename = Historic_fleet.csv

And a props.conf file like:

[NextBus]
category = Custom
disabled = false
LOOKUP-fleet = Historic_fleet vehicle_id

This all works fine. However, there are occasionally other vehicles out that are busses. They have their own vehicle_ids that are not in the lookup table. What I want is to have any vehicle_id that is not found to map to a vehicle_type=bus. I'd like to do this automatically so I don't have to do it for every query I write.

I'm aware that a lookup table can have a default value, but how do you specify that a default value of, say, bus, is supposed to go into the vehicle_type field?

Can I do this with static tables? Or must I resort to using an external lookup Python script?

Re: Default values for CSV lookups that don't match?

lguinn2 — Tue, 28 Feb 2017 00:42:30 GMT

The default value that you specify is placed into all fields that are returned from the lookup, when there is no match. Default values work with static tables.

I highly recommend setting a default value for lookup tables. It is an excellent solution to this sort of problem.

Re: Default values for CSV lookups that don't match?

woodcock — Tue, 28 Feb 2017 18:29:38 GMT

There is nothing really automatic that doesn't overkill like @lguinn said, but you can do this manually:

... | lookup YourLookup | fillnull value="bus" vehicle_type

Or like this:

...  | eval vehicle_type="bus" | lookup YourLookup

Or even like this:

... | lookup YourLookup | eval vehicle_type=coalesce(vehicle_type, "bus")

You can add this to a macro (e.g. default_to_bus_vehicle_lookup) to make it centrally manageable and "automaticish".