topic Re: Calculating Timestamp difference between two events with conditions in Splunk Dev

Calculating Timestamp difference between two events with conditions

shaganga — Fri, 21 Jul 2017 14:31:33 GMT

i am almost cracking my head with this scenario. It would be great if you can help.

Issue: i have few fields like timestamp, queuename, msgid, msqid,seq..etc in every entry of the log file.
i need to retrieve the timestamp and msgid from the first line/event where queuename="abc" and retrieve the timestamp and msgid and search in the next events(next lines) where queuename="xyz" and msqid=msgid(of which we got above in the first line) and retrieve the timestamp. Then calculate the timestamp difference

sample log:
2017/07/20 01:43:06.78,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BA02,414D512044455647505030332020202059635ADE2017BA02,p001,,,NATAAU33XXXN2017720591500514979309,,NATAAU33XXXN2017720591500514979309,
2017/07/20 01:43:18.91,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF1,414D512044455647505030332020202059635ADE10044DF1,pacs.008.001.02,,H07KB4306M850R1X,H07KB4315GZ50R21GPPS,,NATAAU33XXXN2017720591500514979309,
2017/07/20 01:43:30.22,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BC02,414D512044455647505030332020202059635ADE2017BC02,p001,,,NATAAU33XXXN201772001500514980144,,NATAAU33XXXN201772001500514980144,
2017/07/20 01:43:30.50,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF2,414D512044455647505030332020202059635ADE10044DF2,pacs.008.001.02,,H07KB43306950R25,H07KB4330CM50R29GPPS,,NATAAU33XXXN201772001500514980144,
2017/07/20 01:43:50.92,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017BC05,414D512044455647505030332020202059635ADE2017BC05,pacs.002.001.03,,H07KB43306950R25,1230123789422017720481500515028412,H07KB4330CM50R29GPPS,,NATAAU33XXXN201772001500514980144
2017/07/20 01:43:53.70,GTAPIO.QUEUE,414D512044455647505030332020202059635ADE10044DF4,414D512044455647505030332020202059635ADE2017BC02,p002,,,H07KB43306950R25,NATAAU33XXXN201772001500514980144,,NATAAU33XXXN201772001500514980144
2017/07/20 01:44:03.83,GFRVTFRI.QUEUE,414D512044455647505030332020202059635ADE2017BC08,414D512044455647505030332020202059635ADE2017BC08,UNKNOWN MSGDEFIDR,,,,,,

Re: Calculating Timestamp difference between two events with conditions

DalJeanis — Tue, 29 Sep 2020 15:02:32 GMT

You need to throw them together in a pot, sort them into desired order and use streamstats .

The trick to making that work is you need to make sure that the xyz record whose _time you want will appear in the sort order immediately before the abc record, and then use |streamstats current=f last(_time) as prior_time by msgid

This code assumes that each msgid has only one abc record, and that you want to match it with the latest xyz record. That means that xyz has to have a sort order immediately before abc, and needs to be sorted on ascending _time...

your base query which gets both abc and xyz records with _time, queuename, msgid, etc 
| eval sortorder = case(queuename="abc",3, queuename="xyz",2, true(),1)
| sort 0 msgid  sortorder _time
| streamstats current=f last(_time) as prior_time last(queuename) as prior_queue by msgid
| where queuename="abc"
| eval time_diff=coalesce(_time - prior_time,0)
| eval warning=if(coalesce(prior_queue,"") !="xyz", "missing xyz record",null())

Re: Calculating Timestamp difference between two events with conditions

niketn — Fri, 21 Jul 2017 20:44:52 GMT

@shaganga, it would be more helpful if you can point out fields like msgid, msqid and queuename in the sample data provided. Also is there a role of seq field for correlating events? Are there any other fields apart from these?

Re: Calculating Timestamp difference between two events with conditions

shaganga — Tue, 29 Sep 2020 15:02:39 GMT

Please find the mappings and portion of logs

timeStamp=2017/07/20 01:43:06.78
queueName=GFSPII.QUEUE
MsgId=414D512044455647505030332020202059635ADE2017BA02
CoreId=414D512044455647505030332020202059635ADE2017BA02
MsgDefId=p001
transactionId=BLANK
ClearSysRef=BLANK
MsgId=NATAAU33XXXN2017720591500514979309
OriginalMsgId=BLANK
InstructionId=NATAAU33XXXN2017720591500514979309
OriginalInstructionId=BLANK

the requirements is below
A. find the record for (queue=GFSPII.QUEUE and MsgDefId=p001 event) and retrieve the timeStamp(01:43:06.78), InstructionId(NATAAU33XXXN2017720591500514979309) for this event
B. Find the record for the queue=GTAPIO.QUEUE and MsgDefId=p002 with the InstructionId=NATAAU33XXXN2017720591500514979309 (which u retrieve in Step A) should match with OriginalInstructionId=NATAAU33XXXN2017720591500514979309 in STEP B and retrieve the timeStamp(01:43:53.70) value for this event (in this example this queue occurs fifth or sixth event in log)
C. provide the difference of timeStamp values which we got in A and B

Is it possible?
1. we have 1000+ queues in the scenarios, where single transaction flow contains five or six events or more
2. we need to calculate how many transactions which are exceed ( difference between timestamps or > 1.2 seconds)

2017/07/20 01:43:06.78,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BA02,414D512044455647505030332020202059635ADE2017BA02,p001,,,NATAAU33XXXN2017720591500514979309,,NATAAU33XXXN2017720591500514979309,
2017/07/20 01:43:18.91,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF1,414D512044455647505030332020202059635ADE10044DF1,pacs.008.001.02,,H07KB4306M850R1X,H07KB4315GZ50R21GPPS,,NATAAU33XXXN2017720591500514979309,
2017/07/20 01:43:30.22,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BC02,414D512044455647505030332020202059635ADE2017BC02,p001,,,NATAAU33XXXN201772001500514980144,,NATAAU33XXXN201772001500514980144,
2017/07/20 01:43:30.50,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF2,414D512044455647505030332020202059635ADE10044DF2,pacs.008.001.02,,H07KB43306950R25,H07KB4330CM50R29GPPS,,NATAAU33XXXN201772001500514980144,
2017/07/20 01:43:50.92,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017BC05,414D512044455647505030332020202059635ADE2017BC05,pacs.002.001.03,,H07KB43306950R25,1230123789422017720481500515028412,H07KB4330CM50R29GPPS,,NATAAU33XXXN201772001500514980144
2017/07/20 01:43:53.70,GTAPIO.QUEUE,414D512044455647505030332020202059635ADE10044DF4,414D512044455647505030332020202059635ADE2017BC02,p002,,,H07KB43306950R25,NATAAU33XXXN201772001500514980144,,NATAAU33XXXN201772001500514980144
2017/07/20 01:44:03.83,GFRVTFRI.QUEUE,414D512044455647505030332020202059635ADE2017BC08,414D512044455647505030332020202059635ADE2017BC08,UNKNOWN MSGDEFIDR,,,,,,
2017/07/20 01:44:04. 7,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF5,414D512044455647505030332020202059635ADE10044DF5,pacs.008.001.02,,H07KB43306950R25,H07KB44040F50S2JGPPS,,NATAAU33XXXN201772001500514980144,
2017/07/20 01:44:19.21,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017BC0B,414D512044455647505030332020202059635ADE2017BC0B,pacs.002.001.03,,H07KB43306950R25,1230123789422017720161500515056712,H07KB44040F50S2JGPPS,,NATAAU33XXXN201772001500514980144
2017/07/20 01:44:19.42,GTNPSO.QUEUE,414D512044455647505030332020202059635ADE10044DF6,414D512044455647505030332020202059635ADE2017BC02,p002,,,H07KB43306950R25,NATAAU33XXXN201772001500514980144,,NATAAU33XXXN201772001500514980144
2017/07/20 01:44:32.53,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BC0E,414D512044455647505030332020202059635ADE2017BC0E,p001,,,NATAAU33XXXN2017720301500515070204,,NATAAU33XXXN2017720301500515070204,
2017/07/20 01:44:33.89,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DF7,414D512044455647505030332020202059635ADE10044DF7,pacs.008.001.02,,H07KB4432EZ50R2P,H07KB4433NT50R01GPPS,,NATAAU33XXXN2017720301500515070204,
2017/07/20 01:44:52.69,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017BC11,414D512044455647505030332020202059635ADE2017BC11,pacs.002.001.03,,H07KB4432EZ50R2P,1230123789422017720501500515090413,H07KB4433NT50R01GPPS,,NATAAU33XXXN2017720301500515070204
2017/07/20 01:44:54.76,GTAPIO.QUEUE,414D512044455647505030332020202059635ADE10044DF9,414D512044455647505030332020202059635ADE2017BC0E,p002,,,H07KB4432EZ50R2P,NATAAU33XXXN2017720301500515070204,,NATAAU33XXXN2017720301500515070204
2017/07/20 01:44:54.70,PFPCSEI.QUEUE,414D512044455647505030332020202059635ADE10044DF8,414D512044455647505030332020202059635ADE10044DF8,UNKNOWN MSGDEFIDR,,,,,,
2017/07/20 01:44:56.75,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002642,414D512044455647505030332020202059635ADE10044DF8,pacs.002.001.06,NATAAU33_A_TST01_ClrSttlmV001_2017-07-20T01:44:55.202Z_00027,,,,,
2017/07/20 01:44:56.80,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002649,414D5120444556504147303320202020596ECF3120002649,pacs.002.001.06,NATAAU33_A_TST01_ClrSttlmV001_2017-07-20T01:44:55.202Z_00027,,,,,
2017/07/20 01:44:57.17,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DFA,414D512044455647505030332020202059635ADE10044DFA,pacs.008.001.02,,H07KB4432EZ50R2P,H07KB44573U50V0DGPPS,,NATAAU33XXXN2017720301500515070204,
2017/07/20 01:44:57.84,GTNPSO.QUEUE,414D512044455647505030332020202059635ADE10044DFB,414D512044455647505030332020202059635ADE2017BC0E,p002,,,H07KB4432EZ50R2P,NATAAU33XXXN2017720301500515070204,,NATAAU33XXXN2017720301500515070204
2017/07/20 01:45:13.14,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017BF02,414D512044455647505030332020202059635ADE2017BF02,pacs.002.001.03,,H07KB4432EZ50R2P,1230123789422017720101500515110717,H07KB44573U50V0DGPPS,,NATAAU33XXXN2017720301500515070204
2017/07/20 01:45:13.26,GTNPSO.QUEUE,414D512044455647505030332020202059635ADE10044DFC,414D512044455647505030332020202059635ADE10044DFC,p002,,,H07KB4432EZ50R2P,NATAAU33XXXN2017720301500515070204,,NATAAU33XXXN2017720301500515070204
2017/07/20 01:45:26.43,GFSPII.QUEUE,414D512044455647505030332020202059635ADE2017BF05,414D512044455647505030332020202059635ADE2017BF05,p001,,,NATAAU33XXXN2017720241500515124090,,NATAAU33XXXN2017720241500515124090,
2017/07/20 01:45:26.76,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044DFD,414D512044455647505030332020202059635ADE10044DFD,pacs.008.001.02,,H07KB4526C350R0M,H07KB4526KF50R0QGPPS,,NATAAU33XXXN2017720241500515124090,
2017/07/20 01:45:47.10,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017CF02,414D512044455647505030332020202059635ADE2017CF02,pacs.002.001.03,,H07KB4526C350R0M,1230123789422017720441500515144771,H07KB4526KF50R0QGPPS,,NATAAU33XXXN2017720241500515124090
2017/07/20 01:45:48.23,GTAPIO.QUEUE,414D512044455647505030332020202059635ADE10044E69,414D512044455647505030332020202059635ADE2017BF05,p002,,,H07KB4526C350R0M,NATAAU33XXXN2017720241500515124090,,NATAAU33XXXN2017720241500515124090
2017/07/20 01:45:48.06,PFPCSEI.QUEUE,414D512044455647505030332020202059635ADE10044E68,414D512044455647505030332020202059635ADE10044E68,UNKNOWN MSGDEFIDR,,,,,,
2017/07/20 01:45:48.98,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002742,414D512044455647505030332020202059635ADE10044E68,pacs.002.001.06,NATAAU33_A_TST01_ClrSttlmV001_2017-07-20T01:45:48.295Z_00028,,,,,
2017/07/20 01:45:49.01,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002749,414D5120444556504147303320202020596ECF3120002749,pacs.002.001.06,NATAAU33_A_TST01_ClrSttlmV001_2017-07-20T01:45:48.295Z_00028,,,,,
2017/07/20 01:45:50.29,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044E6A,414D512044455647505030332020202059635ADE10044E6A,pacs.008.001.02,,H07KB4526C350R0M,H07KB45503P50V1LGPPS,,NATAAU33XXXN2017720241500515124090,
2017/07/20 01:45:52.78,GTNPSO.QUEUE,414D512044455647505030332020202059635ADE10044E6B,414D512044455647505030332020202059635ADE2017BF05,p002,,,H07KB4526C350R0M,NATAAU33XXXN2017720241500515124090,,NATAAU33XXXN2017720241500515124090
2017/07/20 01:46:05.61,GFPIRSI.QUEUE,414D512044455647505030332020202059635ADE2017CF05,414D512044455647505030332020202059635ADE2017CF05,pacs.002.001.03,,H07KB4526C350R0M,123012378942201772031500515163148,H07KB45503P50V1LGPPS,,NATAAU33XXXN2017720241500515124090
2017/07/20 01:46:06. 7,GTNPSO.QUEUE,414D512044455647505030332020202059635ADE10044E6C,414D512044455647505030332020202059635ADE10044E6C,p002,,,H07KB4526C350R0M,NATAAU33XXXN2017720241500515124090,,NATAAU33XXXN2017720241500515124090
2017/07/20 01:52:05.40,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF312000274F,414D5120444556504147303320202020596ECF312000274F,pacs.008.001.05,ANZBAU3L_A_TST01_ClrSttlmV001_2017-07-20T01:52:04.871Z_00033,,,,,
2017/07/20 01:52:07.97,PFPCSEI.QUEUE,414D512044455647505030332020202059635ADE10044ED6,414D512044455647505030332020202059635ADE10044ED6,UNKNOWN MSGDEFIDR,ANZBAU3L_A_TST01_ClrSttlmV001_2017-07-20T01:52:04.871Z_00033,,,,,
2017/07/20 01:52:09.04,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002759,414D5120444556504147303320202020596ECF3120002759,pacs.002.001.06,ANZBAU3L_A_TST01_ClrSttlmV001_2017-07-20T01:52:04.871Z_00033,,,,,
2017/07/20 01:52:09.23,GTPIRO.QUEUE,414D512044455647505030332020202059635ADE10044ED8,414D512044455647505030332020202059635ADE10044ED8,pacs.008.001.02,,H07KB5205CH5112L,H07KB52094W50T02GPPS,,20161109110033562894499,
2017/07/20 01:54:14.43,PTPCSRO.QUEUE,414D5120444556504147303320202020596ECF3120002553,414D5120444556504147303320202020596ECF3120002553,pacs.008.001.05,ANZBAU3L_A_TST01_ClrSttlmV001_2017-07-20T01:54:13.909Z_00036,,,,,
2017/07/20 01:54:15.52,PFPCSEI.QUEUE,414D512044455647505030332020202059635ADE10044ED9,414D512044455647505030332020202059635ADE10044ED9,UNKNOWN MSGDEFIDR,ANZBAU3L_A_TST01_ClrSttlmV001_2017-07-20T01:54:13.909Z_00036,,,,,
2017/07/20

looking forward -thx

Re: Calculating Timestamp difference between two events with conditions

niketn — Sat, 22 Jul 2017 15:57:38 GMT

@shaganga, through requirement A, B , C seems like you want to extract two events and correlate them together. This can be done in single shot using stats as preferred method (there are other correlation techniques like transaction as well but might not be suitable for your scenario).

Few clarifications though, Your example with field names seems to have two MsgId fields in the same event, is that a typo, also first MsgId is same as CoreId? Is CoreId field available/same in both Event A and Event B
MsgId=414D512044455647505030332020202059635ADE2017BA02
MsgId=NATAAU33XXXN2017720591500514979309

It would have been more useful if you had added one sample each (with field names) of both Event A and Event B which are supposed to be correlated. Since with the requirements A, B, C the data provided is not being correlated.

Re: Calculating Timestamp difference between two events with conditions

shaganga — Mon, 24 Jul 2017 02:48:14 GMT

This not working @DalJeanis.. difference i see ZERO.

Re: Calculating Timestamp difference between two events with conditions

niketn — Mon, 24 Jul 2017 04:55:59 GMT

@shaganga, This might give time difference as zero since there are no correlated event in your raw data example based on the conditions you have provided. Please give examples/field names on only the two events you want to correlate. So that we can ensure providing you with exact query and ensure that we are not missing anything.