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!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...