Getting Data In

Default values for CSV lookups that don't match?

plucas_splunk
Splunk Employee
Splunk Employee

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?

0 Karma

woodcock
Esteemed Legend

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".

0 Karma

lguinn2
Legend

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...