Splunk Search

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

namrithadeepak
Path Finder

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:

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

namrithadeepak
Path Finder

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

nabeel652
Builder

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
0 Karma

nabeel652
Builder

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

0 Karma

namrithadeepak
Path Finder

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?

0 Karma

nabeel652
Builder

Yes.
Extract it as JobStartorEnd and then use CASE statement

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

0 Karma

nabeel652
Builder

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

0 Karma

nabeel652
Builder

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.

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