this certainly turned out to be a mind teaser.
Now, I have used a custom CSV (named memdt) file to upload data, I want to use that first and then move to the real time ingestion. I have overwritten some of the data timestamps towards the end by mistake bu the results are correct and the earlier ones work perfect.
sr Mem time
1 User 01-02-2019 01:54:18
2 System 01-02-2019 01:54:19
3 User 01-02-2019 01:54:28
4 User 01-02-2019 01:54:48
5 NA 01-02-2019 01:54:54
6 Agent 01-02-2019 01:55:15
7 User 01-02-2019 01:55:51
8 User 01-02-2019 01:56:01
9 User 01-02-2019 01:56:02
10 Agent 01-02-2019 01:56:33
11 Agent 01-02-2019 01:57:10
12 User 01-02-2019 01:57:16
13 User 01-02-2019 01:57:21
14 User 01-02-2019 01:57:39
15 Agent 01-02-2019 02:00:50
16 User 01-02-2019 02:01:47
17 User 01-02-2019 02:05:37
18 User 01-02-2019 02:05:39
19 User 01-02-2019 02:05:43
20 System 01-02-2019 02:07:13
21 Agent 01-02-2019 02:11:13
22 System 01-02-2019 02:11:17
23 User 01-02-2019 01:54:12
24 System 01-02-2019 01:54:13
25 NA 01-02-2019 01:54:19
26 Agent 01-02-2019 01:54:27
27 Agent 01-02-2019 01:54:33
28 User 01-02-2019 01:54:34
29 User 01-02-2019 01:54:55
30 Agent 01-02-2019 01:55:02
31 User 01-02-2019 01:55:12
32 User 01-02-2019 01:55:35
33 Agent 01-02-2019 01:56:22
34 User 01-02-2019 01:56:42
35 Agent 01-02-2019 01:57:04
36 User 01-02-2019 01:57:16
37 Agent 01-02-2019 01:57:57
38 System 01-02-2019 02:00:39
here is the code
sourcetype="memdt"| sort sr
| table Mem,time
| where Mem="User" OR Mem="Agent"| streamstats current=f last(Mem) as prev_mem| eval time=if(Mem=prev_mem,0,time),Mem=if(Mem=prev_mem,0,Mem)| where Mem !="0"| fields Mem,time
| streamstats current=f last(Mem) as prev_mem,last(time) as prev_time| eval combo=case(prev_mem="User",prev_mem+ " " +prev_time+"*" + Mem+" "+time) |fields combo| fillnull value=0 | where combo !="0" | streamstats count as row| makemv delim="*" combo| mvexpand combo
| rex field=combo "(?<Mem>.*?)\s"| rex field=combo "\s+(?<time>.*)"
| fields Mem,time,diff,row
| streamstats current=f last(row) as prev_row| streamstats current=f last(time) as prev_time| eval time1=strptime(time,"%d-%m-%Y %H:%M:%S")|eval time2=strptime(prev_time,"%d-%m-%Y %H:%M:%S")
| eval diff=time1-time2
| eval diff=case(Mem="Agent",round(diff))
| eval Set="Set"+row
| rename time as "Response Time"
| fields Set,Mem,"Response Time",diff
| reverse
| filldown
| reverse| eval final_combo=Set+"*"+Mem+"#"+'Response Time'+"$"+diff+"@"+" "
| streamstats count as row
| fields final_combo,
| makemv delim="@" final_combo
| mvexpand final_combo
|rex field=final_combo "(?<Set>.*?)\*+(?<Mem>.*?)\#+(?<Response_Time>.*?)\$+(?<Diff>.*)"| streamstats count as row
| eval flg=case(row%4=0 OR Diff>0,"keep")
| where flg="keep"
| fields Set,Mem,Response_Time,Diff
and here is the output, screen grab. don't get concerned about the last results, i did a mistake in copying your time values to the csv and hence the discrepancy. What I want you to focus on is the time format, I use - but in your real time data you need to use /
