last 30 days daily count based on filed

[dhavamanis]

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

[martin_mueller · Accepted solution]

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.

[dhavamanis]

This is working fine. Thank you so much!.

[somesoni2]

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

[martin_mueller]

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.

[dhavamanis]

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

[martin_mueller]

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().

[dhavamanis]

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(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

[dhavamanis]

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

Last_Log_In Date - Count(
Last_login_date)

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(Last_Log_In,"%Y-%m-%d"),"%Y-%m-%d") |stats count by tdate

[martin_mueller]

What kind of result are you looking for?

[dhavamanis]

Please find the sample csv data ingested into splunk,

id,Primary Email,Email_Verified,Facebook_ID,Twitter_ID,Username,Gender,Birth_Month,Birth_Day,Birth_Year,Prefix,First_Name,Middle_Name,Last_Name,Suffix,Primary Address_Line 1,Address_Line 2,Primary Address_City,Primary Address_State,Address_ZIP,Address_Country,Address_Primary,Primary Address_Type,Primary_Phone,Mobile_Phone,Account_Creation_Date,Last_Log_In,Last_Account_Update_Date,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

[richgalloway]

Perhaps something like this?

<search> earliest=-30d | stats count(Last_Log_In) as Log_In_Counts by date_mday

[richgalloway]

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

[dhavamanis]

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

[martin_mueller]

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