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

[namrithadeepak]

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.

[somesoni2 · Accepted solution]

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

[namrithadeepak]

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.

[somesoni2]

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

[nabeel652]

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

[nabeel652]

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

[namrithadeepak]

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?

[nabeel652]

Yes.
Extract it as JobStartorEnd and then use CASE statement

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

[nabeel652]

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

[nabeel652]

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.