Splunk Search

Analyze where the users "looking for" information in our application

jyab6z
Path Finder

Our log looks like as following after first filter:

Date...Time...UserID...Function...Level 1...Level 2...Section...
20190227 03:56:22:788 [ftjmvf0534faqmyhbwp51e0d] - Function => [level one]10[/level one][level two]50[/level two][section]0089[/section]
20190227 03:55:09:933 [bmmwjmls4uxxr0ypem1ietiw] - Function => [level one]01[/level one][level two]35[/level two][section]5000[/section]
20190227 03:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function => [level one]06[/level one][level two]01[/level two][section]4033[/section]
20190227 03:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function => [level one]06[/level one][level two]01[/level two][section]4063[/section]
20190227 03:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function => [level one]08[/level one][level two]05[/level two][section]5084[/section]
20190227 03:44:56:292 [p4gcrhvhaqib4wm4c0ask0jh] - Function => [level one]18[/level one][level two]55[/level two][section]0030[/section]
20190227 03:33:15:450 [htwym1erwjqn04d2hnhwbuxa] - Function => [level one]06[/level one][level two]01[/level two][section]4063[/section]
20190227 03:33:07:554 [htwym1erwjqn04d2hnhwbuxa] - Function => [level one]06[/level one][level two]01[/level two][section]4033[/section]
20190227 03:30:12:723 [mcgx31a2u0q4jnlhmr4luxuf] - Function => [level one]05[/level one][level two]20[/level two][section]0087[/section]
20190227 03:28:30:050 [h0a0zwhtr3o1ennw1fasoekf] - Function => [level one]18[/level one][level two]15[/level two][section]5310[/section]
20190227 03:23:57:447 [h0a0zwhtr3o1ennw1fasoekf] - Function => [level one]10[/level one][level two]50[/level two][section]0025[/section]
20190227 03:09:53:089 [p5zuajzkinfga5tm2nf4g5d1] - Function => [level one]03[/level one][level two]35[/level two][section]7507[/section]
20190227 03:09:18:810 [p5zuajzkinfga5tm2nf4g5d1] - Function => [level one]03[/level one][level two]35[/level two][section]5245[/section]
20190227 02:57:04:675 [s3spxd4hsjqibkbywlrhd4og] - Function => [level one]01[/level one][level two]01[/level two][section]5105[/section]
20190227 02:53:54:123 [s3spxd4hsjqibkbywlrhd4og] - Function => [level one]01[/level one][level two]05[/level two][section]5106[/section]
20190227 02:53:49:646 [l44x4bvvs2siajkkjw5rdwfv] - Function => [level one]01[/level one][level two]99[/level two][section]5802[/section]
20190227 02:53:15:358 [l44x4bvvs2siajkkjw5rdwfv] - Function => [level one]01[/level one][level two]99[/level two][section]5802[/section]
20190227 02:52:45:112 [l44x4bvvs2siajkkjw5rdwfv] - Function => [level one]01[/level one][level two]99[/level two][section]5802[/section]
20190227 02:52:40:848 [s3spxd4hsjqibkbywlrhd4og] - Function => [level one]04[/level one][level two]05[/level two][section]0231[/section]
20190227 02:52:34:945 [l44x4bvvs2siajkkjw5rdwfv] - Function => [level one]01[/level one][level two]30[/level two][section]5000[/section]

What I want to analyze is where the users "looking for" information in our application.
The hypothesis is, same user, same date, and same Level 1, if the Time difference between two events are less than x secounds, then the user is "looking for" level 2 information in the certain level 1. Show the bar graph with level 1 as x-axis and count as y-axis.

Is there anyone who can help me with the code? Is this possible?
Thanks in advance!

Tags (1)
0 Karma
1 Solution

DavidHourani
Super Champion

Hi @damann

What you're looking for is something like this (wasn't easy, fun building it):

YourBaseSearch | stats count first(_time) AS initialTime last(_time) AS endTime by UserID date levelOne | eval x=endTime-initialTime | where x> YourDefinedValue | fields count levelOne

First YourBaseSearch grabs the events, then stats gets the count, first time and last time for each group of user, date and level one as you mentioned above. Then the eval calculates x which is the difference between first and last time and finally the pretty IF you were looking for using where. Once all that is done fields will keep only the two fields you need for your chart.

Let me know if that helps !

Cheers,
David

View solution in original post

0 Karma

DavidHourani
Super Champion

Hi @damann

What you're looking for is something like this (wasn't easy, fun building it):

YourBaseSearch | stats count first(_time) AS initialTime last(_time) AS endTime by UserID date levelOne | eval x=endTime-initialTime | where x> YourDefinedValue | fields count levelOne

First YourBaseSearch grabs the events, then stats gets the count, first time and last time for each group of user, date and level one as you mentioned above. Then the eval calculates x which is the difference between first and last time and finally the pretty IF you were looking for using where. Once all that is done fields will keep only the two fields you need for your chart.

Let me know if that helps !

Cheers,
David

0 Karma

jyab6z
Path Finder

Hi David,

It works! Thank you! I had to add | convert timeformat="%H:%M:%S" dur2sec(Time) before your code in search because I don't use _time, thereafter I used stats count in the end to plot my graph instead.
So, following code works for me, Time is defined by myself:
YourBaseSearch | convert timeformat="%H:%M:%S" dur2sec(Time) | stats count first(Time) AS initialTime last(Time) AS endTime by UserID date levelOne | eval x=endTime-initialTime | where x> YourDefinedValue | stats count by levelOne

Thank you again David and I will accept the answer.

0 Karma

jyab6z
Path Finder

Hi David,

Thank you for your answer! I will try it as soon as possible and get back to you!

0 Karma

damann
Communicator

Did you already made a field extraction for your fields userid, level_one and level_two?

Otherwise you can use this regex which works for me with your provided sample data:

basesearch | rex "\[(?<userid>\w+)\].*one\](?<level_one>\d+).*two\](?<level_two>\d+).*tion\](?<section>\d+)"

After that you can easily create your bar chart by just counting the events by level_one which should answer your question for

Show the bar graph with level 1 as x-axis and count as y-axis.

| stats count by level_one
0 Karma

jyab6z
Path Finder

Hi Damann,

Thank you for your answer! Appreciate it!
Actually, I got stuck on the if-statement and calculate the time difference between events.
Any idea how to express them?

Thank you again for your contribution!

0 Karma

tiagofbmm
Influencer

Use delta command for that

0 Karma

tiagofbmm
Influencer

would you explain the hypothesis more clearly please ?

0 Karma

jyab6z
Path Finder

Yes, each event is a mouse click in our application where the user navigate to find the information they need. Users need to click level 1 to expand the level 2 and so on.
Our hypothesis is:
If our information in the application are structured well, users will find information by clicking correct level 1 and level 2 with few mouse click and navigate to relevant information.
If the users clicking into a sertain level 1 and quickly clicking through level 2, that means users don't know where the information are and looking for it.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...