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..
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
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
thanks works fine
Hi @varunghai,
Glad to help you.
Can you please accept the answer to close this question and upvote answer?
Happy Splunking
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
You should post this comment as a answer. Is the best query search for this problem.