Splunk Search

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

varunghai
Engager

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:
alt text

Lookup file:
CronJobLookup.csv

Sample output:
alt text

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

Tags (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

varunghai
Engager

thanks works fine

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @varunghai,

Glad to help you.

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

Happy Splunking

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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

thiagodede
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...