Splunk Search

How to compare data with last 7 days of data?

mahbs
Path Finder

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.

Tags (2)

493669
Super Champion

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 *
0 Karma

mahbs
Path Finder

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

0 Karma

493669
Super Champion

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
0 Karma

mayurr98
Super Champion

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!

0 Karma

mahbs
Path Finder

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

0 Karma

mayurr98
Super Champion

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...