Type of Visitor (New or Returning) -> Can be done with single Splunk query?

[lpolo]

Sampling Period = Daily

MAC addresses with 1 count are considered new visitors.

MAC addresses with more than one count are considered returned visitors.

historical_lookup.csv:

time    MAC Count
Day1    01  1
Day1    02  1
Day1    03  1
Day1    04  1

The following steps are executed as a data input script that is scheduled to run once per day.
Can these steps be replaced by a single splunk query?
Thanks.
Lp

Step 1:

earliest=-1d@d latest=@d index=si_raw splunk_server=myindexer 
 | rex field=MAC "(?<MAC>.*?)\""
 | dedup MAC
 | eval time=strftime(_time, "%m/%d/%Y")
 | stats count by MAC time 
 | outputlookup Today_lookup.csv

Result set:

time    MAC Count
Day2    05  1
Day2    01  1
Day2    04  1
Day2    06  1

Step 2:

Obtain a joined lookup table by joining Today_lookup.csv with historical_lookup.csv

|inputlookup historical_lookup.csv
|outputlookup append=true Today_lookup.csv

Result set:

time    MAC Count
Day1    01  1
Day1    02  1
Day1    03  1
Day1    04  1
Day2    05  1
Day2    01  1
Day2    04  1
Day2    06  1

Step 3:

Delete old historical

|outputlookup historical_lookup.csv

Step 4:

Create new historical by reducing Today_lookup table and write result to historical_lookup.csv:

|inputlookup Today_lookup.csv|stats count by MAC
|eval time=strftime(now(), "%m/%d/%Y")|table time MAC count
|outputlookup historical_lookup.csv

Result set:

time    MAC Count
Day2    01  2
Day2    02  1
Day2    03  1
Day2    04  2
Day2    05  1
Day2    06  1

Step 5:

Delete Today_lookup.csv

|outputlookup Today_lookup.csv

[yannK]

Another solution is to use summary indexing, and to save each day the list of MAC addresses (eventually with a count).

then you have 2 options :

• when you need, search over all the summary for the | stats first(_time) AS recent and last(_time) AS oldest by MAC that you you have the day for each MAC address to figure if they came today for the first time or not.
• do that during the summary generation and save each MAC address of the day with a columns for this. (longer to generate, but faster to compare)

[lpolo]

The approach you are presenting will create a summary index that will grow exponentially. The script presented in the question maps and reduce. Your approach does not reduce the result set.

The question is:
Can the steps presented in the question be replaced by a single splunk query?

More or less like forum member gkanapathy answered question:

http://splunk-base.splunk.com/answers/9333/how-to-summary-index-unique-visitor-sessions-in-iis-every...

Thanks,
Lp