topic Re: How to Combine search query with a lookup file with one common field in Splunk Search

How to Combine search query with a lookup file with one common field

varunghai — Tue, 29 Sep 2020 16:53:34 GMT

Hi,

i want to combine the results from my search query with a lookup table that i have uploaded. They both have 1 column in common

Search Query:
index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Starting
| table CronJobName _time

| rename _time as time1

| eval StartTime=strftime(time1,"%m/%d/%y %H:%M:%S")
| Join

[

search index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Finished | table CronJobName _time | rename _time as time2 | eval EndTime=strftime(time2,"%m/%d/%y %H:%M:%S")

]
| table CronJobName StartTime EndTime|dedup CronJobName

Sample output:

Lookup file:
CronJobLookup.csv

Sample output:

i have tried both of them individually and they work perfectly fine, so there is no issue with the current query.
The column which is common in both is called "CronJobName"

I want to join both these and create a table which has columns- CronJobName Expected_STart_Time Expected_End_Time StartTime EndTime Job_Frequency etc..

Re: How to Combine search query with a lookup file with one common field

harsmarvania57 — Fri, 17 Nov 2017 10:39:56 GMT

Hi @varunghai,

Can you please try this single query

index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d | transaction CronJobName startswith="Starting" endswith="Finished"
| dedup CronJobName
| eval EndTime_ep=_time + duration 
| eval StartTime=strftime(_time, "%d-%m-%Y %H:%M:%S"), EndTime=strftime(EndTime_ep, "%d-%m-%Y %H:%M:%S")
| fields CronJobName StartTime EndTime
| lookup CronJobLookup.csv CronJobName AS CronJobName OUTPUT CronJobName AS Lookup_CronJobName Expected_STart_Time Expected_End_Time StartTime EndTime Job_Frequency
| where isnotnull(Lookup_CronJobName)
| fields - Lookup_CronJobName

EDIT: Updated from table to fields

Re: How to Combine search query with a lookup file with one common field

kamlesh_vaghela — Fri, 17 Nov 2017 10:41:48 GMT

Hi @varunghai,

Can you please try below search??

YOUR_FIRST_SEARCH
| append [ inputlookup CronJobLookup.csv | table CronJobName Expected_STart_Time Expected_End_Time StartTime EndTime Job_Frequency  ]
| stats values(CronJobName) as CronJobName values(Expected_STart_Time) as Expected_STart_Time values(Expected_End_Time) as Expected_End_Time values(StartTime) as StartTime values(EndTime) as EndTime values(Job_Frequency) as Job_Frequency by CronJobName

Change lookupfile column name as per your need.

Thanks

Re: How to Combine search query with a lookup file with one common field

varunghai — Fri, 17 Nov 2017 16:37:43 GMT

thanks works fine

Re: How to Combine search query with a lookup file with one common field

kamlesh_vaghela — Fri, 17 Nov 2017 16:40:11 GMT

Hi @varunghai,

Glad to help you.

Can you please accept the answer to close this question and upvote answer?

Happy Splunking

Re: How to Combine search query with a lookup file with one common field

thiagodede — Fri, 17 Nov 2017 19:45:34 GMT

You should post this comment as a answer. Is the best query search for this problem.