Hi,
I've got a query that's failing at the "where" statement. I'm trying to show data in the last 7 days based on data i've imported. This is the query:
source=* host="xxx" index="xxx" sourcetype="xxx" "Issue Type"="Bug" | streamstats dc(source) as distinct_source | head (distinct_source == 1) | eval NewTime=strptime(Created,"%d/%b/%y %H:%M %p") | eval _time=NewTime | eval epoch7days_ago=relative_time(now(), "-7d@d") | where _time>epoch7days_ago
I'm not sure why I'm getting no results at the where statement.
can you share sample output of below query:
source=* host="xxx" index="xxx" sourcetype="xxx" "Issue Type"="Bug" | streamstats dc(source) as distinct_source | head (distinct_source == 1) |table Created *
The date in the Created field has the format: 12/02/2018 10:30
try this:
source=* host="xxx" index="xxx" sourcetype="xxx" "Issue Type"="Bug"
| streamstats dc(source) as distinct_source
| head (distinct_source == 1)
| eval NewTime=strptime(Created,"%d/%m/%Y %H:%M")
| eval time=NewTime
| eval epoch7days_ago=relative_time(now(), "-7d@d")
| where time>epoch7days_ago
hey you can try something like this !
source=* host="xxx" index="xxx" sourcetype="xxx" "Issue Type"="Bug"
| streamstats dc(source) as distinct_source
| head (distinct_source == 1)
| eval NewTime=strptime(Created,"%d/%b/%y %H:%M %p")
| eval time=NewTime
| eval epoch7days_ago=relative_time(now(), "-7d@d")
| where time>epoch7days_ago
As you have assigned Newtime
to _time
it will automatically convert epoch time to readable time format and you can not compare readable time and epoch time.
You can see that using below run anywhere search
| gentimes start=-1 | eval NewTime=strptime(starthuman,"%A %b %d %H:%M:%S %y") |table NewTime | eval _time=NewTime
let me know if this helps!
Hi @mayurr98, sorry, this didn't work. I didn't quite understand what you did here, unfortunately.
Hey if you have the date in 12/02/2018 10:30 format then you should use %d/%m/%Y %H:%M
. Substitute this in the query provided by me.
In my run anywhere example, I took the time converted in epoch and assigned to _time just to show that you can not assign epoch time to _time so in order to assign epoch time you should use other custom time field. So that is the reason I used time
instead of _time
.
I hope you understand this.