<query>msg.jobName="RLMMTP*" "\"message"\" "\"RECORD PROCESSED"\" | spath msg.jobName | eval day = strftime(_time, "%d") | stats earliest(timestamp) as startTime, latest(timestamp) as endTime count by "msg.jobName" | eval startTime=substr(startTime,1,13) | eval ActualStart=strftime(startTime/1000, "%H:%M:%S") | lookup AverageStartTimes.csv msg.jobName as msg.jobName OUTPUT ExpectedStart | stats values(AcutualStart) as ActualStart values(ExpectedStart) as ExpectedStart by "msg.jobName"</query> maybe, it can be trellis visualization. ... View more

I would suggest you create a lookup file that contains a list of the job names and expected start times. That way you can perform a lookup by jobName and return the expected start time dynamically instead of doing it ad hoc in your query. That will also make it more maintainable in the future as you can simply modify the lookup file to reflect the new time. If you need to account for the time for those changes, like it was 15:31:17 for jobName RLMMTP89 only from 1/1/2020 to 1/31/2020 you can make your lookup temporal, but you will need to add the timestamp that reflects when the new expected time is supposed to be selected. Assuming you have do not need to make your lookup temporal, you can create a basic lookup table in Splunk using your sample data: | makeresults count=1 | fields - _time | eval data="RLMMTP90 20:30:11,RLMMTP72 13:22:46" | makemv data delim="," | mvexpand data | rex field=data "(?<msgjobName>[^\s]+) (?<ExpectedStart>[^\e]+)" | fields - data | rename msgjobName as "msg.jobName" | outputlookup expectedJobStarts.csv Now that you have your starter lookup file expectedJobStarts.csv you can put that in your query sourcetype="cf" source=l_cell msg.jobName = RLMMTP* | spath "msg.status.Code" | search "msg.status.Code"=* | spath "msg.Type" | search "msg.Type"=* | spath "msg.message" | search "msg.message"="RECORD PROCESSED" | eval day = strftime(_time, "%d") | stats earliest(timestamp) as startTime, latest(timestamp) as endTime count by msg.jobName | eval startTime=substr(startTime,1,13) | eval startTimeD=strftime(startTime/1000, "%H:%M:%S") | lookup expectedJobStarts.csv "msg.jobName" | eval ActualStart= startTimeD | table msg.jobName ActualStart ExpectedStart I have a couple additional suggestions with your search after looking at it. I suggest you add an index to your search as that will make your search performance significantly better and reduce load on your indexers memory for that search. It also appears that you are querying a json event which should not require the spath commands you are using. Since I don't know the index your data is under I'll use foo as a place holder. Another thing to consider is when you do a stats by the msg.jobName you may be joining together different jobs that run in that same queried time period if you aren't splitting by another field like the "day" field you created or ideally a transaction id that would hopefully be present in your events. index=foo sourcetype="cf" source=l_cell msg.jobName = RLMMTP* msg.status.Code=* msg.Type=* "msg.message"="RECORD PROCESSED" | eval day = strftime(_time, "%d") | stats earliest(timestamp) as startTime, latest(timestamp) as endTime count by day msg.jobName | eval startTime=substr(startTime,1,13) | eval startTimeD=strftime(startTime/1000, "%H:%M:%S") | lookup expectedJobStarts.csv "msg.jobName" | eval ActualStart= startTimeD | table msg.jobName ActualStart ExpectedStart ... View more

CT is 6 hours behind the GMT. So you would need minus the 6 hours from GMT try this: NameOfJob = JOBNAME | spath timestamp | search timestamp=* | eval day = strftime(_time, "%Y-%m-%d") | stats min(timestamp) as StartTimeEpoch, max(timestamp) as EndTimeEpoch by NameOfJob day | eval StartTimeEpoch=strptime(StartTimeEpoch, "%Y-%m-%dT%H:%M:%S") | eval EndTimeEpoch=strptime(EndTimeEpoch, "%Y-%m-%dT%H:%M:%S") | eval duration=(EndTimeEpoch-StartTimeEpoch) | eval minutes = duration/60 | eval hours = minutes/60 | eval StartTimeEpoch=StartTimeEpoch-21600 | eval EndTimeEpoch=EndTimeEpoch-21600 | eval StartTimeEpochD_CT = strftime(StartTimeEpoch,"%Y-%m-%dT%H:%M:%S") | eval EndTimeEpochD_CT = strftime(EndTimeEpoch, "%Y-%m-%dT%H:%M:%S") | sort by StartTimeEpochD_CT ... View more

I have given this a try. It looks like it gives me the same results. I have even tried using _time instead of timestamp within the the eval date statement, this returns only the first date's(within the date range) values. Any idea where to go from here? Thank you ... View more

Try this |union [ your query1] [ your query2] [ your query2] |stats max(*) as * by NameOfJob ... View more

Both queries provide exactly what I need. Thank you greatly for your help. ... View more

Ok, i don't think we're getting anywhere here. Your search should run every 5 minutes LOOKING BACK 5 over the last minutes ago, e.g. 1:00-1:05, 1:05-1:10, 1:10-1:15, and so on. Why would a job that finished more than 5 minutes ago get returned by your search? I don't understand how that is. You're not explaining that at all. But if it is returning ALL of your data every 5 minutes for whatever strange reason, did you try filtering by the end time like i showed above? That would exclude any results where the end time is greater than 5 minutes ago. If your job runs once a day, I have no idea how the alert would be firing more than once with that filter in place Without more details, I can't help any further. You just keep saying the same thing w/o adding anymore info. ... View more

If it's fixed then you can use eval to set a field to a fixed value. ... View more