Splunk Search

Help with Stats and time

qewqre
Explorer

good day everyone,

I have been wrestling with a rather trivial task in Splunk but have not been able to progress with the task at all.

sample data

_time	fields.opco	fields.msisdn	name
2020-08-14T17:45:33.133+0200	JK	567787546132	get /subscription
2020-08-14T17:45:31.229+0200	JK	567880331982	post /signature/login/rio
2020-08-14T17:42:29.931+0200	JK	567980891094	get /subscription
2020-08-14T17:57:41.387+0200	JK	567584306164	get /subscription
2020-08-14T17:57:33.748+0200	JK	567584306164	get /subscription
2020-08-14T17:48:42.669+0200	JK	567584306164	get /subscription
2020-08-14T17:48:29.289+0200	JK	567584306164	get /subscription
2020-08-14T18:20:05.791+0200	KL	86603681561	get /subscription
2020-08-14T18:19:49.900+0200	KL	86603681561	get /subscription
2020-08-14T18:11:28.953+0200	JK	567715786742	get /subscription
2020-08-14T18:11:29.907+0200	JK	567827673378	get /subscription
2020-08-14T18:04:56.286+0200	JK	567796828080	post /signature/login/rio
2020-08-14T18:04:35.562+0200	JK	567796828080	post /signature/login/rio
2020-08-14T18:04:24.930+0200	JK	567789001801	get /subscription
2020-08-14T17:43:10.003+0200	JK	567789001801	get /subscription
2020-08-14T17:43:10.076+0200	JK	567557863786	get /subscription
2020-08-14T17:43:07.001+0200	JK	567551398328	get /subscription
2020-08-14T17:43:07.000+0200	JK	567423617929	get /subscription
2020-08-14T17:43:06.923+0200	JK	567796033325	get /subscription
2020-08-14T17:43:01.029+0200	JK	567980891094	get /subscription
2020-08-14T17:42:49.594+0200	KL	86605019808	get /subscription
2020-08-14T17:27:51.366+0200	JK	567879774893	get /subscription
2020-08-14T17:27:26.210+0200	JK	567879774893	get /subscription
2020-08-14T18:13:41.686+0200	JK	567861848260	post /signature/login/rio
2020-08-14T18:06:48.951+0200	JK	567788218931	get /subscription
2020-08-14T18:06:48.975+0200	JK	567552857976	get /subscription
2020-08-14T17:58:33.827+0200	JK	567867506086	get /subscription
2020-08-14T17:58:32.337+0200	JK	567956155000	post /signature/login/rio
2020-08-14T17:52:46.935+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:20:59.288+0200	JK	567584306164	get /subscription
2020-08-14T18:20:52.249+0200	JK	567584306164	get /subscription
2020-08-14T17:46:20.107+0200	JK	567551398328	get /subscription
2020-08-14T17:46:09.684+0200	JK	567470914264	get /subscription
2020-08-14T17:46:09.702+0200	JK	567584306164	get /subscription
2020-08-14T17:46:09.687+0200	JK	567551648923	get /subscription
2020-08-14T17:42:17.786+0200	JK	567778433115	post /signature/login/rio
2020-08-14T17:37:19.320+0200	KL	86640139242	get /subscription
2020-08-14T17:37:00.768+0200	KL	86640139242	get /subscription
2020-08-14T18:08:35.981+0200	JK	567876586588	get /subscription
2020-08-14T18:08:35.979+0200	JK	567810273174	get /subscription
2020-08-14T18:08:35.389+0200	JK	567500206810	post /signature/login/rio
2020-08-14T18:08:36.020+0200	JK	567407636991	get /subscription
2020-08-14T17:57:53.020+0200	JK	567584306164	get /subscription
2020-08-14T17:50:40.388+0200	JK	567917632405	post /signature/login/rio
2020-08-14T18:10:50.667+0200	JK	567703863374	get /subscription
2020-08-14T18:10:50.654+0200	JK	567715786742	get /subscription
2020-08-14T18:10:50.667+0200	JK	567881600418	get /subscription
2020-08-14T18:10:50.617+0200	JK	567880741429	get /subscription
2020-08-14T18:24:04.558+0200	JK	567567933884	post /signature/login/rio
2020-08-14T18:15:02.487+0200	KL	86662330588	get /subscription
2020-08-14T18:15:02.569+0200	JK	567470905376	get /subscription
2020-08-14T18:14:48.581+0200	JK	567809477109	post /subscription
2020-08-14T17:45:03.361+0200	KL	86662330588	get /subscription
2020-08-14T17:56:45.728+0200	JK	567470905376	get /subscription
2020-08-14T17:38:26.362+0200	JK	567305469170	post /signature/login/rio
2020-08-14T17:38:01.646+0200	KL	86647857718	get /subscription
2020-08-14T17:26:53.098+0200	KL	86615712753	post /subscription
2020-08-14T17:26:50.862+0200	KL	86615712753	get /subscription
2020-08-14T17:26:45.438+0200	KL	86615712753	get /subscription
2020-08-14T18:10:27.861+0200	JK	567887641030	get /subscription
2020-08-14T18:10:27.428+0200	JK	567801939123	get /subscription
2020-08-14T18:10:27.430+0200	JK	567585572786	get /subscription
2020-08-14T18:10:27.332+0200	JK	567470764538	get /subscription
2020-08-14T18:06:16.111+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.025+0200	JK	567917756570	get /subscription
2020-08-14T18:06:04.078+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.052+0200	JK	567470995350	get /subscription
2020-08-14T18:02:19.052+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:00:56.248+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:47:18.906+0200	JK	567564557805	post /signature/login/rio
2020-08-14T17:47:18.278+0200	JK	567966261887	get /subscription
2020-08-14T17:47:03.101+0200	JK	567966261887	get /subscription
2020-08-14T17:47:01.269+0200	JK	567584306164	get /subscription
2020-08-14T17:38:27.471+0200	KL	86647857718	get /subscription
2020-08-14T17:29:50.125+0200	JK	567470905376	get /subscription
2020-08-14T17:25:24.521+0200	JK	567500459122	post /subscription
2020-08-14T18:17:20.090+0200	JK	567584197420	post /signature/login/rio
2020-08-14T18:17:17.281+0200	JK	567747838596	post /signature/login/rio
2020-08-14T18:13:12.213+0200	JK	567423577129	get /subscription
2020-08-14T18:13:12.174+0200	JK	567742420010	get /subscription
2020-08-14T17:59:41.675+0200	JK	567470905376	get /subscription
2020-08-14T17:59:36.997+0200	JK	567956155000	post /subscription
2020-08-14T17:59:29.992+0200	JK	567557818804	post /signature/login/rio
2020-08-14T17:55:40.125+0200	JK	567767666400	post /subscription
2020-08-14T17:55:32.214+0200	JK	567767666400	get /subscription
2020-08-14T17:48:57.879+0200	JK	567817946465	post /signature/login/rio
2020-08-14T18:16:18.110+0200	JK	567860802514	post /signature/login/rio
2020-08-14T18:15:49.201+0200	KL	86667515678	get /subscription
2020-08-14T18:07:54.987+0200	JK	567932760820	post /signature/login/rio
2020-08-14T18:03:46.199+0200	JK	567788218931	post /signature/login/rio
2020-08-14T18:20:04.568+0200	JK	567584306164	get /subscription
2020-08-14T17:41:56.843+0200	JK	567980891094	get /subscription
2020-08-14T17:41:86.214+0200	KL	86647958263	get /subscription
2020-08-14T17:41:25.224+0200	KL	86647958263	get /subscription
2020-08-14T17:40:11.704+0200	KL	86647857718	get /subscription
2020-08-14T17:30:18.798+0200	JK	567920885230	post /signature/login/rio
2020-08-14T17:30:02.388+0200	JK	567470905376	get /subscription
2020-08-14T17:30:02.472+0200	KL	86662330588	get /subscription
2020-08-14T18:00:03.152+0200	KL	86662330588	get /subscription
2020-08-14T18:00:02.433+0200	JK	567470905376	get /subscription
2020-08-14T17:54:51.290+0200	JK	567767666400	post /signature/login/rio
2020-08-14T17:51:47.324+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:51:24.950+0200	JK	567500888621	post /signature/login/rio
2020-08-14T17:45:03.385+0200	JK	567470905376	get /subscription
2020-08-14T17:45:00.808+0200	JK	567787546132	get /subscription
2020-08-14T18:20:18.387+0200	JK	567795418063	get /subscription
2020-08-14T18:20:09.163+0200	JK	567901352826	post /signature/login/rio
2020-08-14T18:14:10.011+0200	JK	567809477109	post /signature/login/rio
2020-08-14T18:14:01.673+0200	JK	567861848260	post /signature/login/rio
2020-08-14T18:13:59.158+0200	JK	567867506086	get /subscription
2020-08-14T18:06:31.299+0200	JK	567920562320	post /signature/login/rio
2020-08-14T18:24:07.940+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:23:56.917+0200	JK	567411800010	post /signature/login/rio
2020-08-14T17:46:47.371+0200	JK	567584306164	get /subscription
2020-08-14T17:32:07.320+0200	JK	567935974302	post /signature/login/rio
2020-08-14T17:28:10.371+0200	JK	567702810173	get /subscription
2020-08-14T17:25:27.130+0200	JK	567425119829	post /signature/login/rio
2020-08-14T18:07:13.222+0200	JK	567788218931	get /subscription
2020-08-14T18:04:02.099+0200	JK	567557818804	post /signature/login/rio
2020-08-14T17:50:04.910+0200	JK	567776187770	post /signature/login/rio
2020-08-14T17:49:48.920+0200	JK	567867940400	get /subscription
2020-08-14T18:12:12.564+0200	JK	567565637382	post /signature/login/rio
2020-08-14T18:12:12.743+0200	JK	567860802514	get /subscription
2020-08-14T18:11:24.571+0200	JK	567788555612	get /subscription
2020-08-14T18:11:24.563+0200	JK	567989942782	get /subscription
2020-08-14T18:22:36.094+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:22:27.678+0200	JK	567774545333	post /signature/login/rio
2020-08-14T18:22:15.224+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:22:14.321+0200	JK	567860802514	get /subscription
2020-08-14T18:21:51.387+0200	JK	567474128268	get /subscription
2020-08-14T18:13:12.252+0200	JK	567393365552	get /subscription
2020-08-14T18:13:12.150+0200	JK	567464236314	get /subscription
2020-08-14T18:13:12.175+0200	JK	567880915362	get /subscription
2020-08-14T18:12:52.638+0200	JK	567771985693	post /signature/login/rio
2020-08-14T17:43:30.425+0200	JK	567825127859	get /subscription
2020-08-14T17:43:30.457+0200	JK	567833209143	get /subscription
2020-08-14T17:43:30.429+0200	JK	567391420102	get /subscription
2020-08-14T18:09:43.645+0200	JK	567932760820	post /subscription
2020-08-14T18:09:38.910+0200	KL	86677740752	get /subscription
2020-08-14T18:09:33.911+0200	JK	567932760820	get /subscription
2020-08-14T17:53:52.017+0200	JK	567751128114	post /signature/login/rio
2020-08-14T17:50:01.989+0200	JK	567841849391	get /subscription
2020-08-14T18:18:48.027+0200	JK	567771985693	post /subscription
2020-08-14T18:18:39.279+0200	JK	567771985693	get /subscription
2020-08-14T18:13:04.976+0200	JK	567305469170	post /signature/login/rio
2020-08-14T18:12:15.649+0200	JK	567500206810	post /signature/login/rio
2020-08-14T18:12:12.790+0200	JK	567585692861	get /subscription
2020-08-14T17:43:30.439+0200	JK	567787546132	get /subscription
2020-08-14T17:43:31.102+0200	JK	567789001801	get /subscription
2020-08-14T17:30:49.299+0200	JK	567920885230	post /signature/login/rio
2020-08-14T17:26:55.616+0200	KL	86615712753	get /subscription
2020-08-14T18:14:48.163+0200	JK	567861848260	post /subscription
2020-08-14T18:14:45.579+0200	KL	86647857718	get /subscription
2020-08-14T18:14:42.800+0200	JK	567809477109	get /subscription
2020-08-14T18:14:39.856+0200	JK	567861848260	get /subscription
2020-08-14T18:14:37.003+0200	JK	567470905376	get /subscription
2020-08-14T18:14:23.203+0200	JK	567493761701	get /subscription
2020-08-14T18:14:23.219+0200	JK	567391473757	get /subscription
2020-08-14T18:14:23.145+0200	JK	567437561172	get /subscription
2020-08-14T18:09:05.207+0200	JK	567900846961	post /signature/login/rio
2020-08-14T18:08:59.108+0200	JK	567790477774	post /signature/login/rio
2020-08-14T18:06:04.055+0200	JK	567787161505	get /subscription
2020-08-14T18:02:54.972+0200	JK	567788218931	post /signature/login/rio
2020-08-14T17:59:18.805+0200	JK	567956155000	get /subscription
2020-08-14T17:52:52.886+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:49:48.963+0200	JK	567841849391	get /subscription
2020-08-14T18:24:36.672+0200	KL	86722222476	get /subscription
2020-08-14T18:24:27.641+0200	JK	567956696586	get /subscription
2020-08-14T18:21:18.402+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:19:39.256+0200	JK	567584306164	get /subscription
2020-08-14T18:15:39.585+0200	JK	567867506086	get /subscription
2020-08-14T18:15:39.123+0200	JK	567884357880	post /signature/login/rio

 

 

 

earliest=-30d@d latest=now index=tdr_p fields.opco="*" name="post /signature/login/rio" OR name="get /subscription" OR name="post /subscription" 
| chart count by fields.msisdn, name 
| rename "get /subscription" as "Passed_VFID", "post /signature/login/rio" as "Started_RIO", "post /subscription" as "Ordered_eSIM" 
| eval "Started_RIO"=if( Started_RIO>0,1,0) 
| eval Passed_VFID=if( Passed_VFID>0,1,0) 
| eval Ordered_eSIM=if( Ordered_eSIM>0,1,0) 
| fields fields.msisdn, "Started_RIO","Passed_VFID","Ordered_eSIM" 
| eval comment=case( 
Started_RIO=1 and Passed_VFID=0 and Ordered_eSIM=0, "Attempts starting ODA RIO but not going past authentication",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=1, "Customer that ordered but not started from ODA",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=0, "Customers started ODA RIO, authenticated but didn’t order",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=1, "Customers started ODA RIO, authenticated and ordered",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=0, "Customer logged in on Portal (not via RIO) but didn’t order") 
| stats count by comment

 

 

 

 

Getting output like this

 

 

 

comment	count
Attempts starting ODA RIO but not going past authentication	3912
Customer logged in on Portal (not via RIO) but didn’t order	8653
Customer that ordered but not started from ODA	592
Customers started ODA RIO, authenticated and ordered	1661
Customers started ODA RIO, authenticated but didn’t order	832

 

 

 

Now my team wanted this stats day wise for last 30 days.  now I am not sure how I can break this stats day wise. I have tried with bucket _time span=1d but not able do it because of chart i.e " chart count by fields.msisdn, name ".  chart not taking 3rd fields in by condition. i.e chart count by _time,fields.msisdn, name 

Any help is greatly appreciated.

Thanks,

 
 

 

Labels (2)
Tags (1)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

Hi

I just take away couple of lines from sample, but I think that you could get the idea here?

index=_internal 
| head 1
| eval _raw="_time	fields.opco	fields.msisdn	name
2020-08-10T17:45:33.133+0200	JK	567787546132	get /subscription
2020-08-10T17:45:31.229+0200	JK	567880331982	post /signature/login/rio
2020-08-10T17:42:29.931+0200	JK	567980891094	get /subscription
2020-08-10T17:57:41.387+0200	JK	567584306164	get /subscription
2020-08-10T17:57:33.748+0200	JK	567584306164	get /subscription
2020-08-10T17:48:42.669+0200	JK	567584306164	get /subscription
2020-08-10T17:48:29.289+0200	JK	567584306164	get /subscription
2020-08-10T18:20:05.791+0200	KL	86603681561	get /subscription
2020-08-10T18:19:49.900+0200	KL	86603681561	get /subscription
2020-08-10T18:11:28.953+0200	JK	567715786742	get /subscription
2020-08-10T18:11:29.907+0200	JK	567827673378	get /subscription
2020-08-11T18:04:56.286+0200	JK	567796828080	post /signature/login/rio
2020-08-11T18:04:35.562+0200	JK	567796828080	post /signature/login/rio
2020-08-11T18:04:24.930+0200	JK	567789001801	get /subscription
2020-08-11T17:43:10.003+0200	JK	567789001801	get /subscription
2020-08-11T17:43:10.076+0200	JK	567557863786	get /subscription
2020-08-11T17:43:07.001+0200	JK	567551398328	get /subscription
2020-08-11T17:43:07.000+0200	JK	567423617929	get /subscription
2020-08-11T17:43:06.923+0200	JK	567796033325	get /subscription
2020-08-12T17:43:01.029+0200	JK	567980891094	get /subscription
2020-08-12T17:42:49.594+0200	KL	86605019808	get /subscription
2020-08-12T17:27:51.366+0200	JK	567879774893	get /subscription
2020-08-12T17:27:26.210+0200	JK	567879774893	get /subscription
2020-08-12T18:13:41.686+0200	JK	567861848260	post /signature/login/rio
2020-08-12T18:06:48.951+0200	JK	567788218931	get /subscription
2020-08-12T18:06:48.975+0200	JK	567552857976	get /subscription
2020-08-12T17:58:33.827+0200	JK	567867506086	get /subscription
2020-08-12T17:58:32.337+0200	JK	567956155000	post /signature/login/rio
2020-08-12T17:52:46.935+0200	JK	567751128114	post /signature/login/rio
2020-08-12T18:20:59.288+0200	JK	567584306164	get /subscription
2020-08-12T18:20:52.249+0200	JK	567584306164	get /subscription
2020-08-12T17:46:20.107+0200	JK	567551398328	get /subscription
2020-08-12T17:46:09.684+0200	JK	567470914264	get /subscription
2020-08-13T17:46:09.702+0200	JK	567584306164	get /subscription
2020-08-13T17:46:09.687+0200	JK	567551648923	get /subscription
2020-08-13T17:42:17.786+0200	JK	567778433115	post /signature/login/rio
2020-08-13T17:37:19.320+0200	KL	86640139242	get /subscription
2020-08-13T17:37:00.768+0200	KL	86640139242	get /subscription
2020-08-13T18:08:35.981+0200	JK	567876586588	get /subscription
2020-08-13T18:08:35.979+0200	JK	567810273174	get /subscription
2020-08-13T18:08:35.389+0200	JK	567500206810	post /signature/login/rio
2020-08-13T18:08:36.020+0200	JK	567407636991	get /subscription
2020-08-14T17:57:53.020+0200	JK	567584306164	get /subscription
2020-08-14T17:50:40.388+0200	JK	567917632405	post /signature/login/rio
2020-08-14T18:10:50.667+0200	JK	567703863374	get /subscription
2020-08-14T18:10:50.654+0200	JK	567715786742	get /subscription
2020-08-14T18:10:50.667+0200	JK	567881600418	get /subscription
2020-08-14T18:10:50.617+0200	JK	567880741429	get /subscription
2020-08-14T18:24:04.558+0200	JK	567567933884	post /signature/login/rio
2020-08-14+T18:15:02.487+0200	KL	86662330588	get /subscription
2020-08-14T18:15:02.569+0200	JK	567470905376	get /subscription
2020-08-14T18:14:48.581+0200	JK	567809477109	post /subscription
2020-08-14T17:45:03.361+0200	KL	86662330588	get /subscription
2020-08-14T17:56:45.728+0200	JK	567470905376	get /subscription
2020-08-14T17:38:26.362+0200	JK	567305469170	post /signature/login/rio
2020-08-14T17:38:01.646+0200	KL	86647857718	get /subscription
2020-08-14T17:26:53.098+0200	KL	86615712753	post /subscription
2020-08-14T17:26:50.862+0200	KL	86615712753	get /subscription
2020-08-14T17:26:45.438+0200	KL	86615712753	get /subscription
2020-08-14T18:10:27.861+0200	JK	567887641030	get /subscription
2020-08-14T18:10:27.428+0200	JK	567801939123	get /subscription
2020-08-14T18:10:27.430+0200	JK	567585572786	get /subscription
2020-08-14T18:10:27.332+0200	JK	567470764538	get /subscription
2020-08-14T18:06:16.111+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.025+0200	JK	567917756570	get /subscription
2020-08-14T18:06:04.078+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.052+0200	JK	567470995350	get /subscription
2020-08-14T18:02:19.052+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:00:56.248+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:47:18.906+0200	JK	567564557805	post /signature/login/rio
2020-08-14T18:15:39.123+0200	JK	567884357880	post /signature/login/rio"
| multikv forceheader=1
| rex "^(?<date>\d+-\d+-\d+)T"
| rename COMMENT AS "Previous set sample data for testing"
| eval fields_msisdn=date . ";" . fields_msisdn
| chart count over fields_msisdn by name
| rename "get /subscription" as "Passed_VFID", "post /signature/login/rio" as "Started_RIO", "post /subscription" as "Ordered_eSIM" 
| eval "Started_RIO"=if( Started_RIO>0,1,0) 
| eval Passed_VFID=if( Passed_VFID>0,1,0) 
| eval Ordered_eSIM=if( Ordered_eSIM>0,1,0) 
| fields fields_msisdn, "Started_RIO","Passed_VFID","Ordered_eSIM" 
| eval foo=split(fields_msisdn,";"), date = mvindex(foo, 0), fields_msisdn = mvindex(foo,1)
| eval comment=case( 
Started_RIO=1 and Passed_VFID=0 and Ordered_eSIM=0, "Attempts starting ODA RIO but not going past authentication",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=1, "Customer that ordered but not started from ODA",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=0, "Customers started ODA RIO, authenticated but didn’t order",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=1, "Customers started ODA RIO, authenticated and ordered",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=0, "Customer logged in on Portal (not via RIO) but didn’t order") 
| chart count over comment by date

 

This is little bite different than using real events from index. 

As you already has try you should use "bin span=1d _time" for groupping data to one day chunks. In this example I just use that rex with date. So you must change it.

Main idea here is combine _time with fields_msisdn so you could still calculate count and include time here.

r. Ismo

View solution in original post

isoutamo
SplunkTrust
SplunkTrust

Hi

can you give sample dataset to us to better help you?

r. Ismo

0 Karma

qewqre
Explorer

Thanks for your reply. this sample data

 

_time	fields.opco	fields.msisdn	name
2020-08-14T17:45:33.133+0200	JK	567787546132	get /subscription
2020-08-14T17:45:31.229+0200	JK	567880331982	post /signature/login/rio
2020-08-14T17:42:29.931+0200	JK	567980891094	get /subscription
2020-08-14T17:57:41.387+0200	JK	567584306164	get /subscription
2020-08-14T17:57:33.748+0200	JK	567584306164	get /subscription
2020-08-14T17:48:42.669+0200	JK	567584306164	get /subscription
2020-08-14T17:48:29.289+0200	JK	567584306164	get /subscription
2020-08-14T18:20:05.791+0200	KL	86603681561	get /subscription
2020-08-14T18:19:49.900+0200	KL	86603681561	get /subscription
2020-08-14T18:11:28.953+0200	JK	567715786742	get /subscription
2020-08-14T18:11:29.907+0200	JK	567827673378	get /subscription
2020-08-14T18:04:56.286+0200	JK	567796828080	post /signature/login/rio
2020-08-14T18:04:35.562+0200	JK	567796828080	post /signature/login/rio
2020-08-14T18:04:24.930+0200	JK	567789001801	get /subscription
2020-08-14T17:43:10.003+0200	JK	567789001801	get /subscription
2020-08-14T17:43:10.076+0200	JK	567557863786	get /subscription
2020-08-14T17:43:07.001+0200	JK	567551398328	get /subscription
2020-08-14T17:43:07.000+0200	JK	567423617929	get /subscription
2020-08-14T17:43:06.923+0200	JK	567796033325	get /subscription
2020-08-14T17:43:01.029+0200	JK	567980891094	get /subscription
2020-08-14T17:42:49.594+0200	KL	86605019808	get /subscription
2020-08-14T17:27:51.366+0200	JK	567879774893	get /subscription
2020-08-14T17:27:26.210+0200	JK	567879774893	get /subscription
2020-08-14T18:13:41.686+0200	JK	567861848260	post /signature/login/rio
2020-08-14T18:06:48.951+0200	JK	567788218931	get /subscription
2020-08-14T18:06:48.975+0200	JK	567552857976	get /subscription
2020-08-14T17:58:33.827+0200	JK	567867506086	get /subscription
2020-08-14T17:58:32.337+0200	JK	567956155000	post /signature/login/rio
2020-08-14T17:52:46.935+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:20:59.288+0200	JK	567584306164	get /subscription
2020-08-14T18:20:52.249+0200	JK	567584306164	get /subscription
2020-08-14T17:46:20.107+0200	JK	567551398328	get /subscription
2020-08-14T17:46:09.684+0200	JK	567470914264	get /subscription
2020-08-14T17:46:09.702+0200	JK	567584306164	get /subscription
2020-08-14T17:46:09.687+0200	JK	567551648923	get /subscription
2020-08-14T17:42:17.786+0200	JK	567778433115	post /signature/login/rio
2020-08-14T17:37:19.320+0200	KL	86640139242	get /subscription
2020-08-14T17:37:00.768+0200	KL	86640139242	get /subscription
2020-08-14T18:08:35.981+0200	JK	567876586588	get /subscription
2020-08-14T18:08:35.979+0200	JK	567810273174	get /subscription
2020-08-14T18:08:35.389+0200	JK	567500206810	post /signature/login/rio
2020-08-14T18:08:36.020+0200	JK	567407636991	get /subscription
2020-08-14T17:57:53.020+0200	JK	567584306164	get /subscription
2020-08-14T17:50:40.388+0200	JK	567917632405	post /signature/login/rio
2020-08-14T18:10:50.667+0200	JK	567703863374	get /subscription
2020-08-14T18:10:50.654+0200	JK	567715786742	get /subscription
2020-08-14T18:10:50.667+0200	JK	567881600418	get /subscription
2020-08-14T18:10:50.617+0200	JK	567880741429	get /subscription
2020-08-14T18:24:04.558+0200	JK	567567933884	post /signature/login/rio
2020-08-14T18:15:02.487+0200	KL	86662330588	get /subscription
2020-08-14T18:15:02.569+0200	JK	567470905376	get /subscription
2020-08-14T18:14:48.581+0200	JK	567809477109	post /subscription
2020-08-14T17:45:03.361+0200	KL	86662330588	get /subscription
2020-08-14T17:56:45.728+0200	JK	567470905376	get /subscription
2020-08-14T17:38:26.362+0200	JK	567305469170	post /signature/login/rio
2020-08-14T17:38:01.646+0200	KL	86647857718	get /subscription
2020-08-14T17:26:53.098+0200	KL	86615712753	post /subscription
2020-08-14T17:26:50.862+0200	KL	86615712753	get /subscription
2020-08-14T17:26:45.438+0200	KL	86615712753	get /subscription
2020-08-14T18:10:27.861+0200	JK	567887641030	get /subscription
2020-08-14T18:10:27.428+0200	JK	567801939123	get /subscription
2020-08-14T18:10:27.430+0200	JK	567585572786	get /subscription
2020-08-14T18:10:27.332+0200	JK	567470764538	get /subscription
2020-08-14T18:06:16.111+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.025+0200	JK	567917756570	get /subscription
2020-08-14T18:06:04.078+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.052+0200	JK	567470995350	get /subscription
2020-08-14T18:02:19.052+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:00:56.248+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:47:18.906+0200	JK	567564557805	post /signature/login/rio
2020-08-14T17:47:18.278+0200	JK	567966261887	get /subscription
2020-08-14T17:47:03.101+0200	JK	567966261887	get /subscription
2020-08-14T17:47:01.269+0200	JK	567584306164	get /subscription
2020-08-14T17:38:27.471+0200	KL	86647857718	get /subscription
2020-08-14T17:29:50.125+0200	JK	567470905376	get /subscription
2020-08-14T17:25:24.521+0200	JK	567500459122	post /subscription
2020-08-14T18:17:20.090+0200	JK	567584197420	post /signature/login/rio
2020-08-14T18:17:17.281+0200	JK	567747838596	post /signature/login/rio
2020-08-14T18:13:12.213+0200	JK	567423577129	get /subscription
2020-08-14T18:13:12.174+0200	JK	567742420010	get /subscription
2020-08-14T17:59:41.675+0200	JK	567470905376	get /subscription
2020-08-14T17:59:36.997+0200	JK	567956155000	post /subscription
2020-08-14T17:59:29.992+0200	JK	567557818804	post /signature/login/rio
2020-08-14T17:55:40.125+0200	JK	567767666400	post /subscription
2020-08-14T17:55:32.214+0200	JK	567767666400	get /subscription
2020-08-14T17:48:57.879+0200	JK	567817946465	post /signature/login/rio
2020-08-14T18:16:18.110+0200	JK	567860802514	post /signature/login/rio
2020-08-14T18:15:49.201+0200	KL	86667515678	get /subscription
2020-08-14T18:07:54.987+0200	JK	567932760820	post /signature/login/rio
2020-08-14T18:03:46.199+0200	JK	567788218931	post /signature/login/rio
2020-08-14T18:20:04.568+0200	JK	567584306164	get /subscription
2020-08-14T17:41:56.843+0200	JK	567980891094	get /subscription
2020-08-14T17:41:86.214+0200	KL	86647958263	get /subscription
2020-08-14T17:41:25.224+0200	KL	86647958263	get /subscription
2020-08-14T17:40:11.704+0200	KL	86647857718	get /subscription
2020-08-14T17:30:18.798+0200	JK	567920885230	post /signature/login/rio
2020-08-14T17:30:02.388+0200	JK	567470905376	get /subscription
2020-08-14T17:30:02.472+0200	KL	86662330588	get /subscription
2020-08-14T18:00:03.152+0200	KL	86662330588	get /subscription
2020-08-14T18:00:02.433+0200	JK	567470905376	get /subscription
2020-08-14T17:54:51.290+0200	JK	567767666400	post /signature/login/rio
2020-08-14T17:51:47.324+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:51:24.950+0200	JK	567500888621	post /signature/login/rio
2020-08-14T17:45:03.385+0200	JK	567470905376	get /subscription
2020-08-14T17:45:00.808+0200	JK	567787546132	get /subscription
2020-08-14T18:20:18.387+0200	JK	567795418063	get /subscription
2020-08-14T18:20:09.163+0200	JK	567901352826	post /signature/login/rio
2020-08-14T18:14:10.011+0200	JK	567809477109	post /signature/login/rio
2020-08-14T18:14:01.673+0200	JK	567861848260	post /signature/login/rio
2020-08-14T18:13:59.158+0200	JK	567867506086	get /subscription
2020-08-14T18:06:31.299+0200	JK	567920562320	post /signature/login/rio
2020-08-14T18:24:07.940+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:23:56.917+0200	JK	567411800010	post /signature/login/rio
2020-08-14T17:46:47.371+0200	JK	567584306164	get /subscription
2020-08-14T17:32:07.320+0200	JK	567935974302	post /signature/login/rio
2020-08-14T17:28:10.371+0200	JK	567702810173	get /subscription
2020-08-14T17:25:27.130+0200	JK	567425119829	post /signature/login/rio
2020-08-14T18:07:13.222+0200	JK	567788218931	get /subscription
2020-08-14T18:04:02.099+0200	JK	567557818804	post /signature/login/rio
2020-08-14T17:50:04.910+0200	JK	567776187770	post /signature/login/rio
2020-08-14T17:49:48.920+0200	JK	567867940400	get /subscription
2020-08-14T18:12:12.564+0200	JK	567565637382	post /signature/login/rio
2020-08-14T18:12:12.743+0200	JK	567860802514	get /subscription
2020-08-14T18:11:24.571+0200	JK	567788555612	get /subscription
2020-08-14T18:11:24.563+0200	JK	567989942782	get /subscription
2020-08-14T18:22:36.094+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:22:27.678+0200	JK	567774545333	post /signature/login/rio
2020-08-14T18:22:15.224+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:22:14.321+0200	JK	567860802514	get /subscription
2020-08-14T18:21:51.387+0200	JK	567474128268	get /subscription
2020-08-14T18:13:12.252+0200	JK	567393365552	get /subscription
2020-08-14T18:13:12.150+0200	JK	567464236314	get /subscription
2020-08-14T18:13:12.175+0200	JK	567880915362	get /subscription
2020-08-14T18:12:52.638+0200	JK	567771985693	post /signature/login/rio
2020-08-14T17:43:30.425+0200	JK	567825127859	get /subscription
2020-08-14T17:43:30.457+0200	JK	567833209143	get /subscription
2020-08-14T17:43:30.429+0200	JK	567391420102	get /subscription
2020-08-14T18:09:43.645+0200	JK	567932760820	post /subscription
2020-08-14T18:09:38.910+0200	KL	86677740752	get /subscription
2020-08-14T18:09:33.911+0200	JK	567932760820	get /subscription
2020-08-14T17:53:52.017+0200	JK	567751128114	post /signature/login/rio
2020-08-14T17:50:01.989+0200	JK	567841849391	get /subscription
2020-08-14T18:18:48.027+0200	JK	567771985693	post /subscription
2020-08-14T18:18:39.279+0200	JK	567771985693	get /subscription
2020-08-14T18:13:04.976+0200	JK	567305469170	post /signature/login/rio
2020-08-14T18:12:15.649+0200	JK	567500206810	post /signature/login/rio
2020-08-14T18:12:12.790+0200	JK	567585692861	get /subscription
2020-08-14T17:43:30.439+0200	JK	567787546132	get /subscription
2020-08-14T17:43:31.102+0200	JK	567789001801	get /subscription
2020-08-14T17:30:49.299+0200	JK	567920885230	post /signature/login/rio
2020-08-14T17:26:55.616+0200	KL	86615712753	get /subscription
2020-08-14T18:14:48.163+0200	JK	567861848260	post /subscription
2020-08-14T18:14:45.579+0200	KL	86647857718	get /subscription
2020-08-14T18:14:42.800+0200	JK	567809477109	get /subscription
2020-08-14T18:14:39.856+0200	JK	567861848260	get /subscription
2020-08-14T18:14:37.003+0200	JK	567470905376	get /subscription
2020-08-14T18:14:23.203+0200	JK	567493761701	get /subscription
2020-08-14T18:14:23.219+0200	JK	567391473757	get /subscription
2020-08-14T18:14:23.145+0200	JK	567437561172	get /subscription
2020-08-14T18:09:05.207+0200	JK	567900846961	post /signature/login/rio
2020-08-14T18:08:59.108+0200	JK	567790477774	post /signature/login/rio
2020-08-14T18:06:04.055+0200	JK	567787161505	get /subscription
2020-08-14T18:02:54.972+0200	JK	567788218931	post /signature/login/rio
2020-08-14T17:59:18.805+0200	JK	567956155000	get /subscription
2020-08-14T17:52:52.886+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:49:48.963+0200	JK	567841849391	get /subscription
2020-08-14T18:24:36.672+0200	KL	86722222476	get /subscription
2020-08-14T18:24:27.641+0200	JK	567956696586	get /subscription
2020-08-14T18:21:18.402+0200	JK	567597940329	post /signature/login/rio
2020-08-14T18:19:39.256+0200	JK	567584306164	get /subscription
2020-08-14T18:15:39.585+0200	JK	567867506086	get /subscription
2020-08-14T18:15:39.123+0200	JK	567884357880	post /signature/login/rio
0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

I just take away couple of lines from sample, but I think that you could get the idea here?

index=_internal 
| head 1
| eval _raw="_time	fields.opco	fields.msisdn	name
2020-08-10T17:45:33.133+0200	JK	567787546132	get /subscription
2020-08-10T17:45:31.229+0200	JK	567880331982	post /signature/login/rio
2020-08-10T17:42:29.931+0200	JK	567980891094	get /subscription
2020-08-10T17:57:41.387+0200	JK	567584306164	get /subscription
2020-08-10T17:57:33.748+0200	JK	567584306164	get /subscription
2020-08-10T17:48:42.669+0200	JK	567584306164	get /subscription
2020-08-10T17:48:29.289+0200	JK	567584306164	get /subscription
2020-08-10T18:20:05.791+0200	KL	86603681561	get /subscription
2020-08-10T18:19:49.900+0200	KL	86603681561	get /subscription
2020-08-10T18:11:28.953+0200	JK	567715786742	get /subscription
2020-08-10T18:11:29.907+0200	JK	567827673378	get /subscription
2020-08-11T18:04:56.286+0200	JK	567796828080	post /signature/login/rio
2020-08-11T18:04:35.562+0200	JK	567796828080	post /signature/login/rio
2020-08-11T18:04:24.930+0200	JK	567789001801	get /subscription
2020-08-11T17:43:10.003+0200	JK	567789001801	get /subscription
2020-08-11T17:43:10.076+0200	JK	567557863786	get /subscription
2020-08-11T17:43:07.001+0200	JK	567551398328	get /subscription
2020-08-11T17:43:07.000+0200	JK	567423617929	get /subscription
2020-08-11T17:43:06.923+0200	JK	567796033325	get /subscription
2020-08-12T17:43:01.029+0200	JK	567980891094	get /subscription
2020-08-12T17:42:49.594+0200	KL	86605019808	get /subscription
2020-08-12T17:27:51.366+0200	JK	567879774893	get /subscription
2020-08-12T17:27:26.210+0200	JK	567879774893	get /subscription
2020-08-12T18:13:41.686+0200	JK	567861848260	post /signature/login/rio
2020-08-12T18:06:48.951+0200	JK	567788218931	get /subscription
2020-08-12T18:06:48.975+0200	JK	567552857976	get /subscription
2020-08-12T17:58:33.827+0200	JK	567867506086	get /subscription
2020-08-12T17:58:32.337+0200	JK	567956155000	post /signature/login/rio
2020-08-12T17:52:46.935+0200	JK	567751128114	post /signature/login/rio
2020-08-12T18:20:59.288+0200	JK	567584306164	get /subscription
2020-08-12T18:20:52.249+0200	JK	567584306164	get /subscription
2020-08-12T17:46:20.107+0200	JK	567551398328	get /subscription
2020-08-12T17:46:09.684+0200	JK	567470914264	get /subscription
2020-08-13T17:46:09.702+0200	JK	567584306164	get /subscription
2020-08-13T17:46:09.687+0200	JK	567551648923	get /subscription
2020-08-13T17:42:17.786+0200	JK	567778433115	post /signature/login/rio
2020-08-13T17:37:19.320+0200	KL	86640139242	get /subscription
2020-08-13T17:37:00.768+0200	KL	86640139242	get /subscription
2020-08-13T18:08:35.981+0200	JK	567876586588	get /subscription
2020-08-13T18:08:35.979+0200	JK	567810273174	get /subscription
2020-08-13T18:08:35.389+0200	JK	567500206810	post /signature/login/rio
2020-08-13T18:08:36.020+0200	JK	567407636991	get /subscription
2020-08-14T17:57:53.020+0200	JK	567584306164	get /subscription
2020-08-14T17:50:40.388+0200	JK	567917632405	post /signature/login/rio
2020-08-14T18:10:50.667+0200	JK	567703863374	get /subscription
2020-08-14T18:10:50.654+0200	JK	567715786742	get /subscription
2020-08-14T18:10:50.667+0200	JK	567881600418	get /subscription
2020-08-14T18:10:50.617+0200	JK	567880741429	get /subscription
2020-08-14T18:24:04.558+0200	JK	567567933884	post /signature/login/rio
2020-08-14+T18:15:02.487+0200	KL	86662330588	get /subscription
2020-08-14T18:15:02.569+0200	JK	567470905376	get /subscription
2020-08-14T18:14:48.581+0200	JK	567809477109	post /subscription
2020-08-14T17:45:03.361+0200	KL	86662330588	get /subscription
2020-08-14T17:56:45.728+0200	JK	567470905376	get /subscription
2020-08-14T17:38:26.362+0200	JK	567305469170	post /signature/login/rio
2020-08-14T17:38:01.646+0200	KL	86647857718	get /subscription
2020-08-14T17:26:53.098+0200	KL	86615712753	post /subscription
2020-08-14T17:26:50.862+0200	KL	86615712753	get /subscription
2020-08-14T17:26:45.438+0200	KL	86615712753	get /subscription
2020-08-14T18:10:27.861+0200	JK	567887641030	get /subscription
2020-08-14T18:10:27.428+0200	JK	567801939123	get /subscription
2020-08-14T18:10:27.430+0200	JK	567585572786	get /subscription
2020-08-14T18:10:27.332+0200	JK	567470764538	get /subscription
2020-08-14T18:06:16.111+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.025+0200	JK	567917756570	get /subscription
2020-08-14T18:06:04.078+0200	JK	567554780915	get /subscription
2020-08-14T18:06:04.052+0200	JK	567470995350	get /subscription
2020-08-14T18:02:19.052+0200	JK	567751128114	post /signature/login/rio
2020-08-14T18:00:56.248+0200	JK	567817946465	post /signature/login/rio
2020-08-14T17:47:18.906+0200	JK	567564557805	post /signature/login/rio
2020-08-14T18:15:39.123+0200	JK	567884357880	post /signature/login/rio"
| multikv forceheader=1
| rex "^(?<date>\d+-\d+-\d+)T"
| rename COMMENT AS "Previous set sample data for testing"
| eval fields_msisdn=date . ";" . fields_msisdn
| chart count over fields_msisdn by name
| rename "get /subscription" as "Passed_VFID", "post /signature/login/rio" as "Started_RIO", "post /subscription" as "Ordered_eSIM" 
| eval "Started_RIO"=if( Started_RIO>0,1,0) 
| eval Passed_VFID=if( Passed_VFID>0,1,0) 
| eval Ordered_eSIM=if( Ordered_eSIM>0,1,0) 
| fields fields_msisdn, "Started_RIO","Passed_VFID","Ordered_eSIM" 
| eval foo=split(fields_msisdn,";"), date = mvindex(foo, 0), fields_msisdn = mvindex(foo,1)
| eval comment=case( 
Started_RIO=1 and Passed_VFID=0 and Ordered_eSIM=0, "Attempts starting ODA RIO but not going past authentication",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=1, "Customer that ordered but not started from ODA",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=0, "Customers started ODA RIO, authenticated but didn’t order",
Started_RIO=1 and Passed_VFID=1 and Ordered_eSIM=1, "Customers started ODA RIO, authenticated and ordered",
Started_RIO=0 and Passed_VFID=1 and Ordered_eSIM=0, "Customer logged in on Portal (not via RIO) but didn’t order") 
| chart count over comment by date

 

This is little bite different than using real events from index. 

As you already has try you should use "bin span=1d _time" for groupping data to one day chunks. In this example I just use that rex with date. So you must change it.

Main idea here is combine _time with fields_msisdn so you could still calculate count and include time here.

r. Ismo

qewqre
Explorer

Thank you very much. your query give me idea to fetch expected things 

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...