Getting Data In

Web Logs Analysis ( apache )

return2health
Engager

Hi there. I'm new to splunk. Having a bit of trouble getting my head around it ( I know SQL well ) .

I want to get google clicks ( contains parameter "gclid" ) - then I want to get the ip address for each click and get all the paid clicks between the initial click and the conversion ( uri_location="success" ) . Is that a bit hard for splunk to do? Can you get me start in the right direction?

Thanks Martin

Tags (2)

sideview
SplunkTrust
SplunkTrust

I also recommend looking at stats, as in Stephen's first example.

<your search> gclid=* | stats list(uri_location) as multi_uri by clientip | search multi_uri="success" 

And the other way I recommend looking at uses the transaction command, which can take little search expressions in the startswith and endswith arguments.

<your search> gclid=* | transaction clientip endswith="uri=success" | search uri=success

and the search on the end is to filter out some unwanted fragment transactions that dont end in success. This may be unnecessary but i think if transaction A ends with success, the transaction command will open another transaction B for that clientip, even though that transaction may never end in success...

At any rate there are some advantages to using transaction over stats, one being that you can do maxspan to appropriately count the same clientip more than once over the range.

However it's good to get really comfortable with stats because it's generally more powerful and generally has better performance than the alternatives.

Stephen_Sorkin
Splunk Employee
Splunk Employee

The simple approach will show clicks even after the "success":

sourcetype=access_combined (uri="*&gclid=*" OR uri_location="success") | stats list(uri_location) by clientip

We can then extend this to exclude the clicks after success:

sourcetype=access_combined (uri="*&gclid=*" OR uri_location="success") | eval success_time = if(uri_location=="success", _time, null()) | eventstats min(success_time) as success_time by clientip | where isnull(success_time) OR _time <= success_time | stats list(uri_location) by clientip
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...