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
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 :
| 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.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:
Thanks,
Lp