topic Re: User visited url and do transaction and get the data, order by time in Splunk Search

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

bijodev1 — Wed, 16 Mar 2022 14:53:28 GMT

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.

Re: User visited url do transaction and get the data based on time

ITWhisperer — Wed, 16 Mar 2022 09:58:44 GMT

Try this

my_search | sort 0 user_id _time | table user_id http_uri

Re: User visited url do transaction and get the data based on time

bijodev1 — Wed, 16 Mar 2022 12:08:53 GMT

@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.

Re: User visited url do transaction and get the data based on time

ITWhisperer — Wed, 16 Mar 2022 12:50:08 GMT

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

Re: User visited url do transaction and get the data based on time

bijodev1 — Wed, 16 Mar 2022 13:11:49 GMT

@ITWhisperer

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

Re: User visited url do transaction and get the data based on time

ITWhisperer — Wed, 16 Mar 2022 13:45:50 GMT

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

Re: User visited url and do transaction and get the data, order by time

bijodev1 — Thu, 17 Mar 2022 05:10:23 GMT

@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

Re: User visited url and do transaction and get the data, order by time

ITWhisperer — Thu, 17 Mar 2022 07:35:23 GMT

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

Re: User visited url and do transaction and get the data, order by time

bijodev1 — Thu, 17 Mar 2022 07:53:32 GMT

@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

Re: User visited url and do transaction and get the data, order by time

ITWhisperer — Thu, 17 Mar 2022 08:38:16 GMT

| 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

Re: User visited url and do transaction and get the data, order by time

bijodev1 — Thu, 17 Mar 2022 09:33:44 GMT

@ITWhisperer not the desired output what I looking for.

Any possibilities of doing it with transaction command

Re: User visited url and do transaction and get the data, order by time

ITWhisperer — Thu, 17 Mar 2022 09:39:09 GMT

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! 😁

Re: User visited url and do transaction and get the data, order by time

bijodev1 — Thu, 17 Mar 2022 12:06:46 GMT

@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

Re: User visited url and do transaction and get the data, order by time

ITWhisperer — Thu, 17 Mar 2022 12:51:20 GMT

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

Re: User visited url and do transaction and get the data, order by time

bijodev1 — Thu, 17 Mar 2022 13:10:52 GMT

@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

Re: User visited url and do transaction and get the data, order by time

ITWhisperer — Thu, 17 Mar 2022 13:20:13 GMT

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?