How to calculate job start/end time of transaction for particular time with an alert when it meets this criteria?

[karthi2809]

how to calculate job start time and job end time of transaction for particular time and to set trigger mail when start time and stop time?
This is my query ,i am getting two events as start time and end time
index=test URI=/member* | head 1 | append [search index=test URI=/member*| tail 1] | transaction URI

How to send email when start time and end time

[smilingajay]

Hi Dal
What if the TransactionEndTime is only in CaptureLocation=Response and TransactionStartTime is only in CaptureLocation=Request

Thanks
AJ

[lfedak_splunk]

Hey @karthi2809, if DalJeanis solved your problem, please don't forget to accept an answer! You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!

[DalJeanis · Accepted solution]

try this...

index=test URI=/member* 
| stats min(_time) as starttime max(_time) as endtime range(_time) as duration by URI

Duration will be in seconds.

However, that doesn't solve your question of sending the start and stop emails. That just assumes that the last record for each will be the end record, which is what your original code was doing.

It would be better to figure out what the records actually look like, and search for them directly.

When you post those, we can help you work out the code.

[karthi2809]

When i use this query i have start time and stop time

index=test_prod URI=/member*| eval StartTime=strftime(_time,"%Y/%m/%d %H:%M:%S")| head 1 | append [search index=test_prod URI=/member*| eval EndTime=strftime(_time,"%Y/%m/%d %H:%M:%S")| tail 1] | transaction URI

9/20/17
4:27:18.570 PM

SPLUNK-TRACE-DateandTime - 2017-09-20 16:27:18.570 ThreadID=200;ThreadIDHex=00000;ThreadName=[WebContainer : 10];Node=MBR2:8448;meta-transid=INTERNAL_4f2d8b-11-48-8d-8e1776;ConsumerSenderID=NA;URI=/member*; TranasactionStartTime=2017-09-20 16:27:15.645;TransactionEndTime=2017-09-20 16:27:18.570;TransactionStatus=SUCCESS;Method=GET;StatusCode=200;Backend=;ErrorMsg=;JDBCInvocation=;JDBCWaitTime=;CacheContentFlag=UNKNOWN;CaptureLocation=Response;

9/20/17
12:30:10.908 PM
SPLUNK-TRACE-DateandTime - 2017-09-20 12:30:10.908 ThreadID=2084;ThreadIDHex=00000;ThreadName=[WebContainer : 2];Node=MBR8:8448;meta-transid=INTERNAL_f63e8-184e-49b-96d-8bbff0e5;ConsumerSenderID=NA;URI=/member*;TranasactionStartTime=2017-09-20 12:30:10.908;TransactionEndTime=NA;TransactionStatus=;Method=GET;StatusCode=;Backend=GetMber, GetContact-dal;ErrorMsg=;JDBCInvocation=;JDBCWaitTime=;CacheContentFlag=UNKNOWN;CaptureLocation=Request;

[DalJeanis]

@Karthi2809 -

Easy enough. All the records have TranasactionStartTime set to the same time. (Note the extra a in Tran a saction in the events.)

If you want to alert that the job has started, you need to key on the job where _time = TranasactionStartTime.

If you want to alert that the job has completed, you need to key on the event where _time = TransactionEndTime.

 index=test URI=/member* 
 | rename COMMENT as "Extract the times from the record" 
 | rex "TranasactionStartTime=(?<start>[^;]+);TransactionEndTime=(?<end>[^;N]*)(;|NA)"
 | eval tranStartTime=strftime(start,","%Y-%m-%d %H:%M:%s.%3N")
 | eval tranEndTime=strftime(end,","%Y-%m-%d %H:%M:%s.%3N")

 | rename COMMENT as "Group the records, clean up duration if the transaction has not completed." 
 | stats min(_time) as starttime, max(tranStartTime) as tranStartTime,
         max(_time) as nowtime, max(tranEndTime) as tranEndTime, range(_time) as duration by URI
 | eval duration=if(isnull(tranEndTime),null(),duration)

 | rename COMMENT as "If more records are possi ble than start and end, only let the start and end through." 
 | where (starttime=nowtime) OR (tranEndTime=nowtime) 

[cmerriman]

when exactly do you want to send an email? You can trigger it when specific conditions are met. Something like every time there is a start time AND end time (ie: two events or duration>0) or if the start time is after a certain hour of the day or on a certain day. can you be more specific?

[karthi2809]

one of the job is running from 12 to 5.In the time i need to find start time and end time .
for ex: if the job starts at 12.30 i need to trigger email as job started .

but for end time i dont know how to find end time and how to send mail for job completed .i dont have any start stop string in the event

[cmerriman]

can you give a sample of you data? you need a trigger every time a job starts and every time a job is completed? How do you know if the job is complete if there is no "complete" (or something similar) string? What metrics define a completed job?

[karthi2809]

That only i dont know how to do.so i tried tail 1 command for last event time