topic Re: how to make lookup fields as static and the results as dynamic from the search query in Splunk Search

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

Kwip — Tue, 17 Apr 2018 05:22:25 GMT

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.

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

p_gurav — Tue, 17 Apr 2018 05:32:43 GMT

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.

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

Kwip — Tue, 17 Apr 2018 05:39:41 GMT

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

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

p_gurav — Tue, 17 Apr 2018 05:42:20 GMT

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

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

Kwip — Tue, 17 Apr 2018 06:22:42 GMT

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

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

deepashri_123 — Tue, 17 Apr 2018 06:25:53 GMT

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!!

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

Kwip — Tue, 17 Apr 2018 06:30:43 GMT

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

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

deepashri_123 — Tue, 17 Apr 2018 06:42:59 GMT

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

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

Kwip — Wed, 18 Apr 2018 02:17:31 GMT

Got the answer from this post.

https://answers.splunk.com/answers/208671/how-to-create-a-table-with-a-static-column-a-and-d.html