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.
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.
This is working fine. Thank you so much!.
sourcetype=csv |eval datetemp=strptime(Last_Log_In,"%Y-%m-%d") | where datetemp>=relative_time(now(), "-90d@d") | stats count by datetemp
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
.
Can you please give us the working example for this case.
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()
.
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
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
What kind of result are you looking for?
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
Perhaps something like this?
<search> earliest=-30d | stats count(Last_Log_In) as Log_In_Counts by date_mday
Perhaps I misunderstand your objective. Please provide some sample events, your attempted query, and expected output.
"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.
Do post some sample events, and what you've tried so far.