Web Logs Analysis ( apache )

[return2health]

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

[sideview]

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]

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