topic Re: Why "where" doesn't work with hight values ? in Splunk Search

Why "where" doesn't work with hight values ?

Abarny — Mon, 24 Apr 2017 09:06:34 GMT

Hi guys,

I have a problem on my request because when i use a short time like 7 days ou 15 days it is right but when i use values like 1 month or 6 months, the request doesn't give me result :

index="xxx"  
| rename "logs{}.*"  as * 
|fields user.lastName,projectId,user.firstName, user.lastName,time,type,date 
| eval acteur='user.firstName'." ".'user.lastName'  | search acteur="AAA" 
|rename projectId as NOVA_ID | join[ search index="gtav2_projects"  projects{}.icma="*"  
|rename projects{}.id as NOVA_ID, projects{}.icma as ICMA ] 
| rename projects{}.title as Title ,projects{}.client.name as "Nom Client" 
| dedup Title |eval dateLimite =relative_time(now(),"-7dmon")   
| convert timeformat="%d/%m/%Y %H:%M" ctime(dateLimite)|  where date > dateLimite 
|table "Nom Client",Title,ICMA,date, dateLimite, NOVA_ID

Thanks you for your help

Re: Why "where" doesn't work with hight values ?

dineshraj9 — Mon, 24 Apr 2017 09:12:34 GMT

Can you check this part of your query -

|eval dateLimite =relative_time(now(),"-7dmon")

Looks like the second parameter needs to be fixed. If you want 7 months, it should be "-7mon".

Re: Why "where" doesn't work with hight values ?

Abarny — Mon, 24 Apr 2017 09:18:03 GMT

it was a typing error, sorry ..

Re: Why "where" doesn't work with hight values ?

dineshraj9 — Mon, 24 Apr 2017 09:21:52 GMT

Try moving the convert statement after the comparison.

 index="xxx"  
 | rename "logs{}.*"  as * 
 |fields user.lastName,projectId,user.firstName, user.lastName,time,type,date 
 | eval acteur='user.firstName'." ".'user.lastName'  | search acteur="AAA" 
 |rename projectId as NOVA_ID | join[ search index="gtav2_projects"  projects{}.icma="*"  
 |rename projects{}.id as NOVA_ID, projects{}.icma as ICMA ] 
 | rename projects{}.title as Title ,projects{}.client.name as "Nom Client" 
 | dedup Title |eval dateLimite =relative_time(now(),"-7mon")   
 |  where date > dateLimite | convert timeformat="%d/%m/%Y %H:%M" ctime(dateLimite)
 |table "Nom Client",Title,ICMA,date, dateLimite, NOVA_ID

Re: Why "where" doesn't work with hight values ?

Abarny — Mon, 24 Apr 2017 09:25:52 GMT

I had try this but the request doesn't give all values

Re: Why "where" doesn't work with hight values ?

Abarny — Mon, 24 Apr 2017 12:32:47 GMT

I try this solution but isn't work too but more easier to debugg I think. Can you tell me why DateAffect is not convert ?

And why this where block again the result ?

index="aaa"  
| rename "logs{}.*"  as * 
|fields user.lastName,projectId,user.firstName, user.lastName,time,type,date 
| eval acteur='user.firstName'." ".'user.lastName'  
| search acteur="XXX" 
| rename projectId as NOVA_ID 
| join[ search index="gtav2_projects"  projects{}.icma="*"  
| rename projects{}.id as NOVA_ID, projects{}.icma as ICMA ] 
| rename projects{}.title as Title ,projects{}.client.name as "Nom Client" 
| dedup Title| convert  timeformat="%d/ %m/ %Y" ctime(date) as DateAffect 
| eval debut=relative_time(now(),"-7mon") 
|  convert timeformat="%d/ %m/ %Y %H:%M" ctime(debut)  
| eval fin=relative_time(now(),"-1d")  
| convert timeformat="%d/ %m/ %Y %H:%M" ctime(fin)  
| where (DateAffect > debut AND DateAffect < fin )  
|table "Nom Client",Title,ICMA,debut,fin,date,DateAffect

Thanks for your answer.