topic Re: How to compare data with last 7 days of data? in Splunk Search

How to compare data with last 7 days of data?

mahbs — Mon, 12 Feb 2018 11:55:02 GMT

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.

Re: How to compare data with last 7 days of data?

mayurr98 — Mon, 12 Feb 2018 12:12:32 GMT

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!

Re: How to compare data with last 7 days of data?

mahbs — Mon, 12 Feb 2018 15:03:25 GMT

Hi @mayurr98, sorry, this didn't work. I didn't quite understand what you did here, unfortunately.

Re: How to compare data with last 7 days of data?

493669 — Mon, 12 Feb 2018 15:18:51 GMT

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 *

Re: How to compare data with last 7 days of data?

mahbs — Mon, 12 Feb 2018 15:48:46 GMT

The date in the Created field has the format: 12/02/2018 10:30

Re: How to compare data with last 7 days of data?

493669 — Mon, 12 Feb 2018 15:54:15 GMT

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

Re: How to compare data with last 7 days of data?

mayurr98 — Tue, 29 Sep 2020 18:00:06 GMT

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.