Splunk Search

Fetch the last 30 days values based on date value from a field.

email2vamsi
Explorer

Hi Experts,

In this search i want to fetch results only from last 30 days to current.
taken_date is one of the field which has got date&time.
max_col1 has got the highest(latest) date value.
So i want to keep max_col1_30 ,to highest(latest) date value - 30 days.
So max_col1_30 will have 30 days old date value.
I want to keep max_col1_30 always to CONSTANT,in this case it should always retain 1574016298.000000.
So that where col1 > max_col1_30 returns events that are only from last 30 days to current.

    base search from JSON.. 
    | eval col1=strptime(taken_date,"%b %d %Y  %H:%M:%S")
    | stats max(col1) as max_col1 by col1
    | eval max_col1_30= max_col1-2629743 
    | where col1 > max_col1_30
    | table  col1 max_col1_30

Results:
    col1              | max_col1_30
    -------------------------------------
    1576545224.000000 | 1573915481.000000
    1576646041.000000 | 1574016298.000000

Thank you.

0 Karma

brabagaza
Explorer

1.Do you want the last 30 days from the most recent timestamp in col1?
or
2.do you want the last 30 days from now?

if it is option 2:

now()

provides an epoch timestamp of current time.

otherwise your search looks pretty good to deal with option1, although

| stats max(col1) AS max_col1
| where col1> max_col1 - (60*60*24*30)

seems to be what you would need if I understand it correctly

Roelof

0 Karma

email2vamsi
Explorer

Hi Roelof,

Please find the actual problem below:
With limit=0 in chart , the chart displays all the available values of taken_date in each row by hostname.
The header will have the corresponding taken_date value for each column.
But I would like to display 30 columns (limit=30) . Columns header should be in descending order of taken_date.
Now unlimited columns are displayed from oldest of taken_date to the latest of the taken_date .

 Current search with problem:
 base search from JSON..
 | eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
 | eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
 | chart limit=0 values(row1) as date1 by hostname col1 
 | fillnull value=NULL

So from the below query, i am trying to get 30 days older to latest value of taken_date in chart.
Here the latest of taken_date can be a week old. The 30 days old date can six months old.
The date&time functions which work only on _time and current time(now()) etc, would not work in this context.

In the below query i am expecting the values from 30 days old of taken_date to latest of taken_date.
30 days old date is derived by substracting 30 days from latest of taken_date.
latest of taken_date can be a week old,2 weeks old or any.

 base search from JSON..
 | eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
 | eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
 | stats max(row1) as max_row1 by row1 hostname max(col1) as max_col1 by col1
 | eval max_row1_30= max_row1-2629743 
 | eval max_col1_30= max_col1-2629743 
 | where row1 > max_row1_30 AND where col1 > max_col1_30
 | chart limit=0 values(row1) as date1 by hostname col1 
 | fillnull value=NULL

Thank you.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If you want a field to contain a constant value then set it to a constant value using eval as in |eval max_col1_30 = 1574016298.
If you want the timestamp of 30 days ago, then use relative_time. For example: | where col1 > relative_time(now(), "-30d").

---
If this reply helps you, Karma would be appreciated.
0 Karma

email2vamsi
Explorer

Hi richgalloway,

Please find the actual problem below:
With limit=0 in chart , the chart displays all the available values of taken_date in each row by hostname.
The header will have the corresponding taken_date value for each column.
But I would like to display 30 columns (limit=30) . Columns header should be in descending order of taken_date.
Now unlimited columns are displayed from oldest of taken_date to the latest of the taken_date .

 Current search with problem:
 base search from JSON..
 | eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
 | eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
 | chart limit=0 values(row1) as date1 by hostname col1 
 | fillnull value=NULL

So from the below query, i am trying to get 30 days older to latest value of taken_date in chart.
Here the latest of taken_date can be a week old. The 30 days old date can six months old.
The date&time functions which work only on _time and current time(now()) etc, would not work in this context.

In the below query i am expecting the values from 30 days old of taken_date to latest of taken_date.
30 days old date is derived by substracting 30 days from latest of taken_date.
latest of taken_date can be a week old,2 weeks old or any.

 base search from JSON..
 | eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
 | eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
 | stats max(row1) as max_row1 by row1 hostname max(col1) as max_col1 by col1
 | eval max_row1_30= max_row1-2629743 
 | eval max_col1_30= max_col1-2629743 
 | where row1 > max_row1_30 AND where col1 > max_col1_30
 | chart limit=0 values(row1) as date1 by hostname col1 
 | fillnull value=NULL

Thank you.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The statement "The date&time functions which work only on _time" is incorrect. The data and time functions work on any field that is in epoch form. Use the strptime function to convert a date/time to epoch form.

One is not limited to using now() in relative_time. Any epoch time can be used.

Try this query.

base search from JSON
| eval latest_date = strptime(taken_date, "%b %d %Y %H:%M:%S:%3N%p")
| streamstats max(latest_date) as latest_date
| eval oldest_date=relative_time(latest_date, "-30d")
| where _time >= oldest_date AND _time <= latest_date
| chart limit=0 values(latest_date) as date1 by hostname oldest_date
| fillnull value=NULL
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...