Splunk Search

How to find records between 1 year period?

lucasdc
New Member

Hi Splunkers!

I have a field which name is "Data Identificada" , all the values inside in this field are strings and the format is d/m/y .I have to make a search that will return for me all the results since today (24/04/2019) until 24/04/2018 and dedup all the records found.

I've written this query but it's still not working.

The output is supposed to be 25 records and it's giving me 313.

Could you guys help me? Thanks

index="db_archer2" earliest=-8760h latest=now()
| fields "Data Identificada"
| dedup "Data Identificada"
| eval Hoje= now()
| eval Hoje= now()-8760
| convert ctime(Hoje) ctime()
| stats dc
Tags (1)
0 Karma
1 Solution

aholzer
Motivator

A few things:

  1. earliest/latest - only apply to the field _time - just want to make sure you are aware of this
  2. you will need to convert "Data identificada" to a numerical value - | eval temporary = strptime('Data identificada', "%d/%m/%Y") This will create a new field "temporary" with the numerical equivalent (epoch time) of the date inside of "Data identificada"
  3. then you can apply a where clause to limit the results - | where temporary>=relative_time(now(),"-1y@d") AND temporary<=relative_time(now(),"@d") This will ensure that the value in temporary is within 1 year "-1y@d" of current time
  4. you can then apply your dedups and anything else you are interested in
  5. doing two evals into the same named field will overwrite the value - doing | eval Hoje = now() | eval Hoje = now()-8760 means you will get only the second value. Also on topic - doing now() - 8760 is the equivalent of subtracting 8760 seconds from current time. Not what you are trying to do

Your final search should look something like this:

index="db_archer2" earliest=-8760h latest=now()
| eval temporary = strptime('Data identificada', "%d/%m/%Y")
| where temporary>=relative_time(now(),"-1y@d") AND temporary<=relative_time(now(),"@d")
| fields "Data Identificada"
| dedup "Data Identificada"

Hope this helps

P.S: you may need to use single quotes (') instead of double quotes (") around the Data Identificada field in the fields and dedup commands. But I'm not 100% sure about this one

View solution in original post

aholzer
Motivator

A few things:

  1. earliest/latest - only apply to the field _time - just want to make sure you are aware of this
  2. you will need to convert "Data identificada" to a numerical value - | eval temporary = strptime('Data identificada', "%d/%m/%Y") This will create a new field "temporary" with the numerical equivalent (epoch time) of the date inside of "Data identificada"
  3. then you can apply a where clause to limit the results - | where temporary>=relative_time(now(),"-1y@d") AND temporary<=relative_time(now(),"@d") This will ensure that the value in temporary is within 1 year "-1y@d" of current time
  4. you can then apply your dedups and anything else you are interested in
  5. doing two evals into the same named field will overwrite the value - doing | eval Hoje = now() | eval Hoje = now()-8760 means you will get only the second value. Also on topic - doing now() - 8760 is the equivalent of subtracting 8760 seconds from current time. Not what you are trying to do

Your final search should look something like this:

index="db_archer2" earliest=-8760h latest=now()
| eval temporary = strptime('Data identificada', "%d/%m/%Y")
| where temporary>=relative_time(now(),"-1y@d") AND temporary<=relative_time(now(),"@d")
| fields "Data Identificada"
| dedup "Data Identificada"

Hope this helps

P.S: you may need to use single quotes (') instead of double quotes (") around the Data Identificada field in the fields and dedup commands. But I'm not 100% sure about this one

lucasdc
New Member

Hi Aholzer!! Thanks for helping me.

The query is working , but it is still not bringing the right result yet!

Splunk finds now 129 records 😕

0 Karma

aholzer
Motivator

@lucasdc can you provide a sample of 3-5 values that are incorrectly showing up in the results, and can you provide a brief explanation as to why you think they shouldn't be showing up?

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 ...