Splunk Search

Help returning the fields with response from user to agent in Mem field

rajaguru2790
Explorer

Need your help to return the fields with the response from user to agent in Mem field.
There are 7 sets of user to agent and please tell me the logic to do to get the output mentioned below.

Output :
Giving set names as set 1 to set n based on data and difference from user to agent response time in secs for respective sets.

Set Mem Response Time   Time Diff-Secs
Set1    User    01/02/2019 01:54:18 AM  57
Set1    Agent   01/02/2019 01:55:15 AM  57

Set2    User    01/02/2019 01:55:51 AM  42
Set2    Agent   01/02/2019 01:56:33 AM  42

Set3    User    01/02/2019 01:57:16 AM  214
Set3    Agent   01/02/2019 02:00:50 AM  214

Set4    User    01/02/2019 02:01:47 AM  566
Set4    Agent   01/02/2019 02:11:13 AM  566

Set5    User    01/02/2019 01:54:12 AM  15
Set5    Agent   01/02/2019 01:54:27 AM  15

Set6    User    01/02/2019 01:54:34 AM  28
Set6    Agent   01/02/2019 01:55:02 AM  28

Set7    User    01/02/2019 01:55:12 AM  70
Set7    Agent   01/02/2019 01:56:22 AM  70

Set8    User    01/02/2019 01:56:42 AM  75
Set8    Agent   01/02/2019 01:57:57 AM  75

Raw Data:

Mem Response Time
User    01/02/2019 01:54:18 AM
System  01/02/2019 01:54:19 AM
User    01/02/2019 01:54:28 AM
User    01/02/2019 01:54:48 AM
NA  01/02/2019 01:54:54 AM
Agent   01/02/2019 01:55:15 AM
User    01/02/2019 01:55:51 AM
User    01/02/2019 01:56:01 AM
User    01/02/2019 01:56:02 AM
Agent   01/02/2019 01:56:33 AM
Agent   01/02/2019 01:57:10 AM
User    01/02/2019 01:57:16 AM
User    01/02/2019 01:57:21 AM
User    01/02/2019 01:57:39 AM
Agent   01/02/2019 02:00:50 AM
User    01/02/2019 02:01:47 AM
User    01/02/2019 02:05:37 AM
User    01/02/2019 02:05:39 AM
User    01/02/2019 02:05:43 AM
System  01/02/2019 02:07:13 AM
Agent   01/02/2019 02:11:13 AM
System  01/02/2019 02:11:17 AM
User    01/02/2019 01:54:12 AM
System  01/02/2019 01:54:13 AM
NA  01/02/2019 01:54:19 AM
Agent   01/02/2019 01:54:27 AM
Agent   01/02/2019 01:54:33 AM
User    01/02/2019 01:54:34 AM
User    01/02/2019 01:54:55 AM
Agent   01/02/2019 01:55:02 AM
User    01/02/2019 01:55:12 AM
User    01/02/2019 01:55:35 AM
Agent   01/02/2019 01:56:22 AM
User    01/02/2019 01:56:42 AM
Agent   01/02/2019 01:57:04 AM
User    01/02/2019 01:57:16 AM
Agent   01/02/2019 01:57:57 AM
System  01/02/2019 02:00:39 AM
0 Karma

Sukisen1981
Champion

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

0 Karma

Sukisen1981
Champion

hi @rajaguru2790 did you try this out?

0 Karma

rajaguru2790
Explorer

Due to System message some difference coming in -6547 and giving wrong response time.

0 Karma

Sukisen1981
Champion

what is -6547?
Is the code working?

0 Karma

rajaguru2790
Explorer

Thanks Suki. It worked,

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!