topic Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join? in Splunk Search

My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

namrithadeepak — Mon, 12 Dec 2016 22:04:46 GMT

I have 2 jobs running daily (DailyDayJob, DailyNightJob) that logs to a common file.
The logs are as given below:

2016-12-09 03:21:19 INFO  DailyDayJob:39 Starting day job before purge
2016-12-09 03:28:10 INFO  DailyDayJob:41 Daily day job completed

2016-12-09 23:21:19 INFO  DailyNightJob:49 Starting night job before purge
2016-12-09 23:27:19 INFO  DailyNightJob:51 Daily night job completed

I want the output in the following format:

The individual queries are working,

source="*Batch_Logs*" "*Starting*" 
| rex field=_raw "(?P<startDate>\d{4}\-\d{2}\-\d{2})\s(?P<startTime>\d{2}\:\d{2}\:\d{2})(?P<batchJobDescription>.*)" 

source="*Batch_Logs*" "*completed*" 
    | rex field=_raw "(?P<endDate>\d{4}\-\d{2}\-\d{2})\s(?P<endTime>\d{2}\:\d{2}\:\d{2})(?P<batchJobDescription>.*)"

When I try append/appendcols/join, Only startDate and startTime gets extracted. endDate,endTime do not get extracted, nor am I able to see events with 'completed'.

source="*Batch_Logs*" "*Starting*"  | rex field=_raw "(?P<startDate>\d{4}\-\d{2}\-\d{2})\s(?P<startTime>\d{2}\:\d{2}\:\d{2})(?P<batchJobDescription>.*)" | append 
    [search source="*Batch_Logs*" "*completed*" 
    | rex field=_raw "(?P<endDate>\d{4}\-\d{2}\-\d{2})\s(?P<endTime>\d{2}\:\d{2}\:\d{2})(?P<batchJobDescription>.*)"]

What am I doing wrong? Please tell me the correct query to get the format in the table, and also to calculate duration.

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

nabeel652 — Mon, 12 Dec 2016 22:39:53 GMT

Hi
Your queries don't show where "Date" and "Job Name" columns are coming from. It looks like the best way would be to use transaction command if you have something like JobID. It would automatically calculate duration.

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

nabeel652 — Mon, 12 Dec 2016 23:14:27 GMT

Use this for transactions. this regex is created from your data. Will show jobs starting and completing status and duration. You can easily modify the output using transformation commands. Cheers 🙂

source="data.log"  |  rex field=_raw "(?\d{4}-\d{2}-\d{2})\s+(?\d{2}:\d{2}:\d{2})\s+INFO\s+(?\w+):(?\d+)\s.*(?(Starting|completed))" | transaction JobID keeporphans=true | table JobDate JobTime JobType JobStartOrEnd duration

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

nabeel652 — Mon, 12 Dec 2016 23:54:31 GMT

Don't know why the fieldnames are being removed from the query. However, you can add the fieldnames in the regex yourself.

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

somesoni2 — Tue, 13 Dec 2016 00:32:29 GMT

Try something like this

source="*Batch_Logs*" "*Starting*" OR "*completed*"
| rex "^(?<timestamp>\d{4}-\d{2}-\d{3}\s\d{2}:\d{2}:\d{2})\s\w+\s+(?<JobName>[^:]+)"
| eval action=if(searchmatch("*Starting*"),"start","end")
| eval Datejob=strftime(_time,"%m/%d/%Y")." ".JobName
| chart values(timestamp) over Datejob by action
| rex field=Datejob "(?<Date>\S+)\s(?<JobName>.+)"
| rex field=start "(?<startDate>\S+)\s(?<startTime>.+)"
| rex field=end "(?<endDate>\S+)\s(?<endTime>.+)"
| eval Duration=strptime(end,"%Y-%m-%d %H:%M:%S")-strptime(start,"%Y-%m-%d %H:%M:%S")
| talbe Date JobName startDate startTime endDate endTime Duration

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

namrithadeepak — Tue, 13 Dec 2016 01:01:32 GMT

Thankyou very much.

I would prefer to have two separate fields, startTime and endTime (in the place of JobStartorEnd).
Is there anyway of doing that?

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

namrithadeepak — Tue, 13 Dec 2016 01:03:22 GMT

Thankyou for the response.

Is there anyway of having two separate searches? I will be handing over the dashboard code to another team with no transition, and it will be easier to maintain if the search queries are separate.

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

nabeel652 — Tue, 13 Dec 2016 01:16:22 GMT

Yes.
Extract it as JobStartorEnd and then use CASE statement

| eval status = CASE(like(JobStartorEnd,"%Starting%"),"Start",1==1,"Ending")

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

somesoni2 — Tue, 13 Dec 2016 01:18:23 GMT

You can use the same query with different base search to get data for different jobs.

 source="*Batch_Logs*" "*Starting*" OR "*completed*" "*DailyDayJob*" | ..rest of the search

and

 source="*Batch_Logs*" "*Starting*" OR "*completed*" "*DailyNightJob*" | ..rest of the search

Re: My two individual searches are working, but why am I unable to combine all extracted fields using append, appendcols, or join?

nabeel652 — Tue, 13 Dec 2016 01:26:05 GMT

However, if you go with the regex I provided you don't need to: This is the output:

JobDate JobTime JobType JobStartOrEnd
12/12/2016 3:21:19 DailyDayJob Starting
9/12/2016 23:27:19 DailyNightJob completed
9/12/2016 23:21:19 DailyNightJob Starting
9/12/2016 3:28:10 DailyDayJob completed
9/12/2016 3:21:19 DailyDayJob Starting