Splunk Search

how to make lookup fields as static and the results as dynamic from the search query

Kwip
Contributor

Hi
I am having some of the fields in my lookup file (csv file). And I want list down the lookup fields along with the search query result.

But I want my lookup fields as static and search results as dynamic.

Say Example,
I am having field A B C in my lookup file, and generating D and E from the search query result. In my dashboard/form panel A, B and C fields should be always visible. Whereas D and E are based on the search result.

My sample query,

index=XXX sourcetype=yyyy status=* job=* 
| lookup dummy.csv Category
| stats latest(status) as CurrentStatus by Category job StartTime EndTime

In the above sample, Category is from lookup table job is the matching point for both lookup and search query. starttime and endtime is from the search result.

So in my dashboard Category and job values should be visible always, other field values may get updated as per the search query.

0 Karma
1 Solution

Kwip
Contributor
0 Karma

deepashri_123
Motivator

Hey@Kwip,

Can you try something like this:

index=XXX sourcetype=yyyy status=* job=* 
| lookup dummy.csv job OUTPUT Category
| search Category="*"
| stats latest(status) as CurrentStatus values(StartTime) values(EndTime) by Category,job 

Let me know if this helps!!

0 Karma

Kwip
Contributor

Hi @deepashri_123
Thanks for your response. It is giving the same result what I have already.

0 Karma

deepashri_123
Motivator

Hi Kwip,

Can you try this:
index=XXX sourcetype=yyyy status=* job=*
| lookup dummy.csv job OUTPUT Category
| stats latest(status) as CurrentStatus values(StartTime) values(EndTime) values(Category) AS Category by job

0 Karma

p_gurav
Champion

Can you try:

 index=XXX sourcetype=yyyy status=* job=* 
 | lookup job  dummy.csv Category
 | stats latest(status) as CurrentStatus list (StartTime) list(EndTime)by Category job 

Also you can try using inputlookup command.

0 Karma

Kwip
Contributor

@p_gurav
Thanks for your response!

In this case, I am getting error like lookup name "job" does not exits. It is considering job as lookup name.

0 Karma

p_gurav
Champion

Extremely sorry. Try this:

  index=XXX sourcetype=yyyy status=* job=* 
  | lookup  dummy.csv job OUTPUT Category
  | stats latest(status) as CurrentStatus list (StartTime) list(EndTime)by Category job 
0 Karma

Kwip
Contributor

Again it is listing down the values which are returned from the search query.

I need Category and job values from my lookup table to be in the table as static irrespective of the search result.

Say example I am having 10 values for job and equivalent category values in my look up file. From the search I am getting the status, StartTime, EndTime fields and its values.

There are scenarios, I will only get status, Startime and endtime only for 5 job and category values from my search result. In such cases my output table will have only 5 rows for which the search given results.

But I want to create like, My table should always show all the values(10 values) of Category and job, Other fields values should get updated upon the search result. So in the above example, my resulted table should be like.

Job Category Status StatTime EndTime
1 -a -RU - 9 -NC
2 -s -SU -5 -11
3 -d -FA -4 NA
4 -f
5 -e
6 -q
7 -v -SU -5 -6
8 -n -RU -4 NC
9 -x
10 -l

0 Karma
Get Updates on the Splunk Community!

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

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...