topic How to use inputlookup and search together ? in Getting Data In

How to use inputlookup and search together ?

Real_captain — Wed, 05 Feb 2025 14:37:43 GMT

HI Team

Is it possible to use the inputlookup of csv file with 7 column and fill the details in those 7 columns using the search command that fetches the data from splunk ??

Examples:

My csv looks like this:
Column1 , Column2
Value A1 , Value B1
Value A2 , Value B2
Value A3 , Value B3
Value A4, Value B4

I need output like below :
Column1 , Column2 , Column3 , Column4
Value A1 , Value B1 , Value C1 , Value D1
Value A2 , Value B2 , Value C2 , Value D2
Value A3 , Value B3 , Value C3 , Value D3
Value A4, Value B4 , Value C4 , Value D4

Values of Column 3 and Column4 are fetched from Splunk using search command and using the key value of Column1.

I've tried to use the below search, but it is not working:

| inputlookup File.csv
| join Column1 type=left
[ | tstats latest(Column3) as START_TIME ,
latest(Column4) as END_TIME
where index = main source = xyz
]
| table Column1 , Column2 , START_TIME , END_TIME

Re: How to use inputlookup and search together ?

bowesmana — Thu, 06 Feb 2025 00:40:15 GMT

Firstly join is almost never a solution to a Splunk problem.

Secondly, you do not have Column1 as an output of your tstats search, so how can it match up Col1+Col2 with the start/end times.

Generally if you want to enrich the start/end times with info from a lookup, you would run the tstats, then lookup the common fields (Column1) from the lookup and output the other fields (Column2)

If you want to end up with all the rows from the lookup in the output, even where there is no data for some of the rows, you would then do a final couple of commands, i.e.

| inputlookup File.csv append=t | stats values(*) as * by Column1

which would then give you all the rows from the lookup and start/end times from data for Column1 found in the tstats search.

Re: How to use inputlookup and search together ?

Real_captain — Thu, 06 Feb 2025 12:10:54 GMT

Thanks @bowesmana for the suggestions.

Can you please let me know how i can use the lookup with the below search.
Below query gives me the results of all the columns if there is a record in the splunk logs with JOBNAME values as A1 and A2.
If there is no record for the jobname A1 and A2, there is no record fetched, and output is blank.

index = main source=xyz (TERM(A1) OR TERM(A2) ) ("- ENDED" OR "- STARTED" )
| rex field=TEXT "((A1-) |(A2-) )(?<Func>[^\-]+)"
| eval Function=trim(Func), DAT = strftime(relative_time(_time, "+0h"), "%d/%m/%Y")
| rename DAT as Date_of_reception
| eval {Function}_TIME=_time
| stats values(Date_of_reception) as Date_of_reception values(*_TIME) as *_TIME by JOBNAME
| table JOBNAME,Description, Date_of_reception ,STARTED_TIME , ENDED_TIME
| sort -STARTED_TIME

We want the output as below even when there is no record in the splunk logs with column1 values as A1 and A2.
When there is no record, fields "Date_of_reception ,STARTED_TIME , ENDED_TIME " should be Blank.

File.csv :
JOBNAME ,Description
A1 , Job A1
A2, Job A2

Desired Output :
JOBNAME,Description, Date_of_reception ,STARTED_TIME , ENDED_TIME
A1 , Job A1 , 05/02/2025 , 12:54:31 , 12:54:40
A2, Job A2 , , ,

Date_of_reception ,STARTED_TIME , ENDED_TIME is blank for A2 because there are no logs in Splunk for the Jobname A2.

Can you please help to update the query to get the desired output:

Current query:

Re: How to use inputlookup and search together ?

bowesmana — Thu, 06 Feb 2025 22:14:26 GMT

So, you would use it like this

index = main source=xyz (TERM(A1) OR TERM(A2) ) ("- ENDED" OR "- STARTED" ) | rex field=TEXT "((A1-) |(A2-) )(?<Func>[^\-]+)" | eval Function=trim(Func), DAT = strftime(relative_time(_time, "+0h"), "%d/%m/%Y") | rename DAT as Date_of_reception | eval {Function}_TIME=_time | stats values(Date_of_reception) as Date_of_reception values(*_TIME) as *_TIME by JOBNAME ``` This adds in all the entries in the lookup at the end of the current results ``` | inputlookup append=t File.csv ``` This then joins all the lookup fields to your result data based on JOBNAME ``` | stats values(*) as * by JOBNAME ``` Now order the fields as needed and sort ``` | table JOBNAME Description Date_of_reception STARTED_TIME ENDED_TIME | sort -STARTED_TIME

i.e. append all the lookup data to the end and collapse it on JOBNAME

Note that your _time handling is a little strange - not sure what you're trying to do, but what's wrong with just

| eval DATE_of_reception=strftime(_time, "%d/%m/%Y")

Note also that if you have more than 1 START_TIME or END_TIME, the sort will not work correctly on the multivalue field.

Re: How to use inputlookup and search together ?

Real_captain — Fri, 07 Feb 2025 11:56:04 GMT

Thanks @bowesmana , it worked for me.

Another question,
Is it possible to fetch only the latest record with latest END_TIME when we have multiple records with different END_TIME.

Currently, if there are 2 records with the different END_TIME for the same JOBNAME, we have 2 records.
Is it possible to display only 1 record per jobname with the latest END_TIME ??

Re: How to use inputlookup and search together ?

bowesmana — Sun, 09 Feb 2025 21:32:04 GMT

I am not sure how you would get two 'records' for the same jobname as all the aggregations you are doing are by JOBNAME. But do you mean you have two (or more) values of END_TIME for the same single JOBNAME in the output.

That would be because you are doing

| stats values(Date_of_reception) as Date_of_reception values(*_TIME) as *_TIME by JOBNAME

i.e. values(*_TIME)... is giving you all the values of the START and END TIME values.

If you just want the latest END_TIME then you just need to use max and min as needed, i.e.

| stats values(Date_of_reception) as Date_of_reception max(END_TIME) as END_TIME min(START_TIME) as START_TIME by JOBNAME

would give you the earliest start and the latest end.

But if you have getting more than one event then any use of values will give you all values, but note also that values() will sort the values in the multivalue field, so bear that in mind.