Splunk Search
Highlighted

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

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
Highlighted

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

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, an upvote would be appreciated.
0 Karma
Highlighted

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

Explorer

Hi richgalloway,

Please find the actual problem below:
With limit=0 in chart , the chart displays all the available values of takendate 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 takendate.
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 takendate 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 takendate to latest of takendate.
30 days old date is derived by substracting 30 days from latest of takendate.
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
Highlighted

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

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, an upvote would be appreciated.
0 Karma
Highlighted

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

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
Highlighted

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

Explorer

Hi Roelof,

Please find the actual problem below:
With limit=0 in chart , the chart displays all the available values of takendate 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 takendate.
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 takendate 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 takendate to latest of takendate.
30 days old date is derived by substracting 30 days from latest of takendate.
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