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.
@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.
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
Thank you, I understood but now sure how to put this on query. Can you please help me.
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
@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 |
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
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
| 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
@ITWhisperer not the desired output what I looking for.
Any possibilities of doing it with transaction command
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! 😁
@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 |
CD_125 | 2 | url/E |
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 |
@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 |
CD_125 | 2 | url/E |
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?
Try this
my_search
| sort 0 user_id _time
| table user_id http_uri