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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...