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

[jyab6z]

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!

[DavidHourani · Accepted solution]

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

[jyab6z]

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.

[jyab6z]

Hi David,

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

[damann]

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

[jyab6z]

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!

[tiagofbmm]

Use delta command for that

[tiagofbmm]

would you explain the hypothesis more clearly please ?

[jyab6z]

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.