topic Re: Lookup with variable Output in Splunk Search

Lookup with variable Output

CarmineCalo — Wed, 17 Jan 2018 13:56:25 GMT

Splunkers!

I'm facing the following use case.

I've a search that return fields like:
- date (month/year)
- AppID
- Availability Cluster
- ...

In a lookup table, I've the connection b/w date (month/Year) and Availability Clusters, like

Date Cluster 1 Cluster 2 Cluster 3
2016-01 100 200 300
2016-02 110 210 310
2016-03 120 220 320
....

Availability Clusters in the search and in the lookup have the same domain.

Now, i need the lookup in the table the value matching both date and Availability Cluster fields related to the events (so the column to be lookup is variable, depending on "Availability Cluster" field content).
How can i do?

Currently the lookup looks something like, don't know how to complete the statement

lookup lookupfile.csv Date as Date OUTPUTNEW ???

Ideas/ suggestions are really appreciated,
Tks!

Carmine

Re: Lookup with variable Output

somesoni2 — Wed, 17 Jan 2018 20:03:27 GMT

Do you control the format/generation of the lookup table? If yes, I would suggest to make the lookup table more linear with just 3 columns, like this

Date,Cluster,Value
2016-01,Cluster 1,100
2016-01,Cluster 2,200
2016-01,Cluster 3,300
....

You'd also need to ensure that timestamp format in Date column matches your data exactly.
That way it'll be easy to lookup your data (...| lookup lookupfile.csv Date as Date Cluster as "Availability Cluster" OUTPUT Value).

If you can't try this workaround (less efficient)

your current search giving fields date, AppID, "Availability Cluster"...
| join type=left Date "Availability Cluster" [| inputlookup lookupfile.csv| untable Date Cluster Value | rename Cluster as "Availability Cluster"]

Re: Lookup with variable Output

CarmineCalo — Thu, 18 Jan 2018 10:04:36 GMT

Clever Idea.

May i code a lookup with 2 parameters as input?
I imagine something like:

| lookup lookupfile.csv Date as Date,  Cluster as Cluster OUTPUTNEW <<FIELD>>as <<FIELD>>

Correct?

Tks!
Carmine

Re: Lookup with variable Output

493669 — Thu, 18 Jan 2018 10:33:20 GMT

yes, so you can write as
your current search giving fields date, AppID, "Availability Cluster"...| lookup lookupfile.csv Date as Date, Cluster as "Availability Cluster" OUTPUTNEW Value