How to edit my search to find a session where a user has visited two different URLs in the same session?

[jh5970]

Hi all,

(URL="xxx.com") OR (URL="zzz.com")  index=logs | timechart span=1d dc(IP)

I am trying to use above search to get sessions that a user has visit two different URLs in the same session?
But for me it looks like it will only search either xxx.com or zzz.com, it might not have both in it. Does anyone know how do we modify the search to find the result for session count that user has visit both URLs in the same session?

Thanks.

[DalJeanis]

1) The word "OR" in capital letters means that either condition will meet the criteria. Use the word "AND" to require BOTH criteria to be met.

Also, to be clear with the computer, when testing the same field twice, put the whole phrase in parenthesis so that it knows which items are being tested together.

 index=logs (URL="xxx.com" OR URL="zzz.com")      means at least one must be present in the event 
 index=logs (URL="xxx.com" AND URL="zzz.com")     means both must be present in the same event

In general, if unspecified, anything on the initial search defaults to "AND".

Not knowing how your events are set up, it's hard to be exact in this code, but this would produce a count of the distinct IP addresses on any given day that had at least one session that included both URLs.

  index=logs (URL="xxx.com" OR URL="zzz.com")  
| stats min(_time) as _time values(URL) as URL by session, IP     
| where like(URL,"%xxx.com") AND like(URL,"%zzz.com")
| timechart span=1d dc(IP)

---

General Advice

Before you spend too much time debugging, you should find a single session that actually has the two URLs you are looking to find together, Narrow down the search with "earliest" and "latest" so that the first line of the search will pick up the events for that session, at least one other session that doesn't have the two together, and not too much more.

Next, add the next line, and make sure the two URLs come out together in a multivalue field called URL, along with all the other fields you need.

Next, make sure that your transaction that has both survives the where clause, and nothing else does.

Finally, add the timechart command and see that it produces a point on the chart.

After all that, then you can go back and run it over a couple of days to see how the data looks.

Clear?

[jh5970]

Hi,

Thanks for the answer. But when I do the stats and timechart, it won't give me any output in statistics and visualization.
Both URLs are from difference sources, but both shared the same field which is IP. that's why I am dc by IP.

My current query would be
(x_URL="xxx.com" AND APP=app AND index=index1) OR (x_URL=yyy.com AND index=index2) | eval Date=strftime(_time,"%Y-%m-%d") | stats dc(x_URL) as URL by x_IP | where URL>1

I am using OR to get both URLs from different source into the same search. and use the dc(x_URL) by x_IP and URL>1 to get the distinct IPs which has both URLs.
But I am having trouble to convert it in timechart since I don't want to know which IP has both URLs presented, I just wanted to know the daily counts.

(x_URL="xxx.com" AND APP=app AND index=index1) OR (x_URL=yyy.com AND index=index2) | eval Date=strftime(_time,"%Y-%m-%d") | stats values(x_URL) as URL by x_IP | where URL>1 | timechart span=1d dc(x_IP)
Above query would give me any output as well.

Anything I should do to modify the query to make it works?

Thanks

[sduff_splunk]

Do you have a session id in the logs, or are you just using the IP.

The following search will filter events that only contain the two URLs in question, and then count the number of unique URLs visited by each IP address. Finally, only keep the rows that have visited both URLs.

index=logs (URL="xxx.com") OR (URL="zzz.com") | stats dc(URL) as URLs by IP | where URLs > 1 

[jh5970]

Hi,

Thanks for your answer. Your answer does gave me the IPs that have both URLs, but I would like to know the daily counts instead of the IPs.

(x_URL="xxx.com" AND APP=app AND index=index1) OR (x_URL=yyy.com AND index=index2) | eval Date=strftime(_time,"%Y-%m-%d") | stats values(x_URL) as URL by x_IP | where URL>1 | timechart span=1d dc(x_IP)
I tried above query, but it doesn't give me any output.

Anything I should do to modify the query to make it works?

Thanks