Splunk Search

last 30 days daily count based on filed

Builder

We have a field with timestamp data like "2013-04-30 19:34:23" as LastLogIn, how do we find last 30 days daily LastLogIn count.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Something like this?

sourcetype=csv | epoch = strftime(Last_Log_In, "%Y-%m-%d %H:%M:%S")
| bin span=1d epoch | where relative_time(now(), "-30d@d") <= epoch
| stats count as Log_In_Counts by epoch | fieldformat epoch = strftime(epoch, "%F")

Row 1 grabs your data and converts your string to an epoch date, row 2 groups that date by day and filters for last 30 days, row 3 runs your counting report and formats the epoch as a user-readable date.

View solution in original post

SplunkTrust
SplunkTrust

Something like this?

sourcetype=csv | epoch = strftime(Last_Log_In, "%Y-%m-%d %H:%M:%S")
| bin span=1d epoch | where relative_time(now(), "-30d@d") <= epoch
| stats count as Log_In_Counts by epoch | fieldformat epoch = strftime(epoch, "%F")

Row 1 grabs your data and converts your string to an epoch date, row 2 groups that date by day and filters for last 30 days, row 3 runs your counting report and formats the epoch as a user-readable date.

View solution in original post

Builder

This is working fine. Thank you so much!.

0 Karma

SplunkTrust
SplunkTrust

sourcetype=csv |eval datetemp=strptime(LastLogIn,"%Y-%m-%d") | where datetemp>=relative_time(now(), "-90d@d") | stats count by datetemp

0 Karma

SplunkTrust
SplunkTrust

Using your example of

sourcetype=csv
| eval datetemp=strftime(strptime(Last_Log_In,"%Y-%m-%d"),"%Y-%m-%d")
| eval nowstring=strftime(now(), "%Y-%m-%d")
| where datetemp>=relative_time(nowstring, "-90d@d")
| stats count by datetemp

I'd change that to

sourcetype=csv
| eval datetemp=strptime(Last_Log_In,"%Y-%m-%d")
| where datetemp>=relative_time(now(), "-90d@d")
| eval datetemp=strftime(datetemp,"%Y-%m-%d")
| stats count by datetemp

I've split up the strptime() and strftime() into two calls and used the epoch timestamp in the middle for where.

0 Karma

Builder

Can you please give us the working example for this case.

0 Karma

SplunkTrust
SplunkTrust

relative_time() cannot deal with formatted time strings, apply the where condition against relative_time(now(), "-90d@d") instead. For the comparison with datetemp you also need to use epoch timestamps, ie make the comparison after strptime() but before strftime().

0 Karma

Builder

I am trying like this and not giving any results, seems something wrong for calculating the last 90 days relative_time(nowstring, "-90d@d"), can you please check this.

sourcetype=csv |eval datetemp=strftime(strptime(LastLogIn,"%Y-%m-%d"),"%Y-%m-%d") | eval nowstring=strftime(now(), "%Y-%m-%d") | where datetemp>=relative_time(nowstring, "-90d@d") | stats count by datetemp

0 Karma

Builder

we need a results for last X number of Days for LastLogIn field count group by LastLogIn date,

LastLogIn Date - Count(
Lastlogindate)

2013-12-30 - 12
2013-12-29 - 1
2013-12-28 - 0
2013-12-27 - 50
2013-12-26 - 101
2013-12-25 - 0
2013-12-24 - 10
2013-12-23 - 8
2013-12-22 - 500
2013-12-21 - 211

Tried with the below query and need to add filter for last 30 days,

sourcetype=csv |eval tdate=strftime(strptime(LastLogIn,"%Y-%m-%d"),"%Y-%m-%d") |stats count by tdate

0 Karma

SplunkTrust
SplunkTrust

What kind of result are you looking for?

0 Karma

Builder

Please find the sample csv data ingested into splunk,

id,Primary Email,EmailVerified,FacebookID,TwitterID,Username,Gender,BirthMonth,BirthDay,BirthYear,Prefix,FirstName,MiddleName,LastName,Suffix,Primary AddressLine 1,AddressLine 2,Primary AddressCity,Primary AddressState,AddressZIP,AddressCountry,AddressPrimary,Primary AddressType,PrimaryPhone,MobilePhone,AccountCreationDate,LastLogIn,LastAccountUpdateDate,Brand_Data
981234,xxx@g.com,,,,xxx9999,f,1,1,1991,,xxxx,,xxxx,,,,test,AK,10011,US,True,,,,2013-07-01 17:01:29,2013-07-01 17:01:31,2013-07-01 17:04:36,ok

0 Karma

SplunkTrust
SplunkTrust

Perhaps something like this?

<search> earliest=-30d | stats count(Last_Log_In) as Log_In_Counts by date_mday
---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

Perhaps I misunderstand your objective. Please provide some sample events, your attempted query, and expected output.

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

Builder

"LastLogIn" is a field not extracted as datemday. need to compare "LastLog_In" with current date and filter by last X number of days. Can you please share that splunk query for this.

0 Karma

SplunkTrust
SplunkTrust

Do post some sample events, and what you've tried so far.

0 Karma