Splunk Search

bin by date that is not the time field

user93
Communicator

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? 

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

user93
Communicator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!