bin by date that is not the time field

[user93]

Hi,

So I have a goal to count user visits, but the log polls too frequently, so we are going to define a visit by one user per day. In this instance the data is not yet in splunk, but on an excel spreadsheet. I'm not very good with excel, so I want to add to splunk and use the bin feature.

I have userid and date. I can use either the time field or the date field and I can reformat the date field, but currently the datefield is mm/d/yyyy. I can reformt if makes it easier. 

Once I have my lookup, how do I use the equivalent bin _time span=1d where the time is now a date field? 

[richgalloway]

The bin command takes a field of your choosing.  Replace _time with the name of your datetime field.  For best results, convert the field into epoch form using strptime before using bin.

[user93]

Thanks Rich! I've only used strptime once, I'm now trying to learn how to strip time M/D/Y

|inputlookup file.csv

|strptime

|bin

|stats count

[richgalloway · Accepted solution]

The strptime function is pretty easy to work with (but I also think regex is easy  😀).

| inputlookup file.csv
| eval datetime=strptime(foo, "%m/%d/%Y")
| bin span=1d datetime
| stats count by datetime
```Make the datetime field human-readable```
| fieldformat datetime=strftime(datetime, "%m/%d/%Y")

See the Search Reference manual for detail about strptime, strftime, and their format strings.