Find a results between two dates (initial date and limit date) .

[lucasdc]

Hi Splunkers , I've tried this query to return events between two specific dates.

Here is the query:

index="db_rsa_archer"
| eval Data Identificada=strptime('Step Due Date', "%d/%m/%Y/")
| eval days = round((now()-dateDue))
| fields "Nome do Projeto"
| table "Nome do Projeto" ,"Data Identificada"
| dedup "Nome do Projeto"

So , I gotta find results between 18/04/2018 until 18/04/2019 (limit date) in this string field (Data Identificada) and then remove the duplicates by "Nome do Projeto"

The result is supposed to be 25 events , but it returns for me 313 events.

Thank you!

[woodcock]

Try this:

index="db_rsa_archer" 
| eval Data_Identificada=strptime('Step Due Date', "%d/%m/%Y")
| where Data_Identificada >= relative_time(now(), "-1y") AND Data_Identificada <= now()
| dedup "Nome do Projeto"
| table "Nome do Projeto" ,"Data Identificada"

P.S. Spaces and Hyphens in field names are evil.

[lucasdc]

Hi woodcock! thanks for helping me.

The query isnt working , the splunk says that there is an error in where command, the expression is malformed.

[cmerriman]

If I understand, you have a field called Step Due Date that has a date formatted %d/%m/%Y and you want to know how many days are between that date and today?

Try this:

index="db_rsa_archer" 
| fields "Step Due Date" "Nome do Projeto"
| rename "Step Due Date" as step_due_date
| eval Data_Identificada=strptime(step_due_date, "%d/%m/%Y")
| eval days = round((now()-Data_Identificada)/86400)
| table "Nome do Projeto" ,"Data_Identificada"
| dedup "Nome do Projeto"

some of the issues were using the wrong field names. let me know if this helps

[lucasdc]

Blockquote

Hi cmerriman! Thanks for helping me! I think that I've told something wrong because the query isn't working.

Actually , 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 (22/04/2019) until 22/04/2018 and dedup all the results found.

that's the answer!

Could you help to build this query? I pay for you! if you dont mind and pass me your number I would appreciate

Thanks

[woodcock]

I forgot an ). I edited and fixed my original answer.

[cmerriman]

before the dedup, try adding a |where days<=365
or is there not even data coming back when you run
index="db_rsa_archer"
| fields "Step Due Date" "Nome do Projeto"
| rename "Step Due Date" as step_due_date
| eval Data_Identificada=strptime(step_due_date, "%d/%m/%Y")
| eval days = round((now()-Data_Identificada)/86400)

based on what you have shown in your question, you have a field called "Step Due Date" that is a string in the format of d/m/y and you create "Data Identificada" from that.