How to create a search for when User visits url, do a transaction, and get the data based on time?

[bijodev1]

Hi Everyone,

I am trying to pull a result per customer, where he/she has visited url based on time_order

I did something like this , I got the result but it is in alphabetical order what I am looking is time_order

my_search 
| transaction user_id startswith=http_uri="/" endswith=http_uri="random.html"
| table user_id http_uri

Also is there any other way other than transaction,I am not sure.

please guide on how this can be achieved. thank you.

[bijodev1]

@ITWhisperer If I do this I will all the http_uri .

let's say http_uri contains A - Z links, I only want how the user visited from E - J links in order by time.

[ITWhisperer]

You could use streamstats to count the instances of E by user. Then reverse the events and use streamstats to count the instances of J by user. Then find the maximum value of J count by user. Then subtract J count from the max J count +1. Then where E count equals adjusted J count, you have the events between E and the next J

[bijodev1]

@ITWhisperer 

Thank you, I understood but now sure how to put this on query. Can you please help me.

[ITWhisperer]

Slight tweak to what I outlined, but try something like this

| sort 0 _time
| streamstats count(eval(url="URL/E")) as eCount global=f by user
| reverse
| streamstats count(eval(url="URL/J")) as jCount global=f by user eCount
| eventstats max(jCount) as jMax by user eCount
| eval jCount=if(jMax>0,jMax-jCount+1,0)
| reverse
| where eCount > 0 AND jCount=1

[bijodev1]

@ITWhisperer thank you for this, but this is not what I am looking for is - to calculate the duration period b/w first(url/E) to first(url/J) or last(url/J) and then order it by _time. for example : 

The user - CD_123 visited from url/E - url/J and took the duration 20seconds which is correct  --- I know this can be calculated by transaction command but in some cases when you see the user - CD_125 - user visited url/E twice and url/J twice and because of that duration shows as 2 seconds which is incorrect. I am not sure how to pull this. sorry for all the confusion.

user                                     duration               url    

CD_123	20	url/E url/F url/H url/I url/J
CD_124	5	url/E url/F url/I url/J
CD_125	2	url/E url/F url/H url/E url/I url/J url/J

[ITWhisperer]

User CD_125 is a very good example. What exactly do you want from such a sequence? You say what you are getting is wrong without explaining why it is wrong.

Also, without timing being shown, I can't tell where the 2 seconds is coming from. Is it from the first E to the first J or the first E to the last J or the second E to the first J etc. and which of these do you want?

For example, what do you want from the following sequence for the same user 

url/E
url/F
url/H
url/J
url/E
url/I
url/J

Please clarify your requirements

[bijodev1]

@ITWhisperer 

My requirement would be to get the duration from first url/E to first url/J also if we can get from first url/E to last url/J

[ITWhisperer]

| sort 0 _time
| streamstats dc(eval(url="URL/E")) as eCount global=f by user
| reverse
| streamstats count(eval(url="URL/J")) as jCount dc(eval(url="URL/J")) as jDCount global=f by user eCount
| eventstats max(jCount) as jMax by user eCount
| eval jeCount=if(jMax>0,jMax-jCount+1,0)

eCount=1 AND jeCount=1 gives you first E to first J

eCount=1 AND jDCount=1 gives you first E to last J

[bijodev1]

@ITWhisperer not the desired output what I looking for.

Any possibilities of doing it with transaction command

[ITWhisperer]

In what way is it not what you were looking for?

To be honest, the transaction command has limitations which why I prefer doing it old school for greater flexibility! 😁

[bijodev1]

@ITWhisperer so basically if we take this data of the user - CD_125

USER_ID.                         TIMESTAMP.     URL

CD_125	05:30:36	URL/E
CD_!25	05:30:38	URL/F
CD_125	05:30:39	URL/H
CD_125	05:30:41	URL/J
CD_125	05:30:43	URL/E
CD_125	05:30:44	URL/I
CD_125	05:30:45	URL/J

what I am looking here is total duration of first URL/E to first URL/J and last URL/E to last URL/J. The output would look like this 

CD_125	5	url/E url/F url/H url/J
CD_125	2	url/E url/I url/J

[ITWhisperer]

What would you expect from this sequence?

USER_ID.                         TIMESTAMP.     URL

CD_125	05:30:36	URL/E
CD_!25	05:30:38	URL/F
CD_125	05:30:39	URL/H
CD_125	05:30:41	URL/E
CD_125	05:30:43	URL/J
CD_125	05:30:44	URL/I
CD_125	05:30:45	URL/J

[bijodev1]

@ITWhisperer I got that result using table. Below is the output what I am looking for.

The output what I am expecting is this :

CD_125	5	url/E url/F url/H url/J
CD_125	2	url/E url/I url/J

[ITWhisperer]

That doesn't answer the question - I changed the order of the second E and the first J - what do you expect to happen in this situation?

[ITWhisperer]

Try this

my_search 
| sort 0 user_id _time
| table user_id http_uri