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? 

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

| 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, Karma 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, Karma 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  :grinning_face:).

| 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, Karma would be appreciated.
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...