Why is eval case not working in my search?

[saurabhkunte]

HI All ,

I hope someone can help me out with a problem I currently see in a query.

I have a Splunk DB Connect query indexing certain data every 4 hours. It's set to do full dump of the results instead of dbmon-tail. So that means there is duplication of data every time the DB Connect scheduled query runs, which is fine since I can dedup those. The output of the SQL query is as follows:

The output also contains logIDs that ran in previous years, that are of little interest to me.

UpdateTime is the column of interest for me, since I will override the _time with the values in UpdateTime in my query to correctly calculate summation of job runs between certain days. In my case, we have a process that starts between 13th day of the month backwards till 23rd day of the last month.

Here is my query that incorrectly includes job IDs that ran in previous years and months along with my condition, so the summation value is incorrect .

Any clues as to why the code also includes jobs from previous years? Thank you.

Explanation of the query:
Extracting the current month and assigning it a variable name now_month.
Replacing the dconnect _time value with UpdateTime to set correct timestamp for JobIDs
Based on that evaluating UpdateTime to select JOb IDs that 13th day of this month to 23rd day of last month, then summing the duration_time

index=summary sourcetype=orc_srv | eval now_month = tonumber(strftime(now(),"%m")) | eval log_time_epoch = tonumber(strptime(log_time,"%Y-%m-%d %H:%M:%S"))
| eval ts = tonumber(strptime(Oracle_SeekTime,"%Y-%m-%d %H:%M:%S"))
| eval ts = log_time_epoch
| dedup ts 
| stats sum(eval(case(((tonumber(strftime(ts,"%m")) == now_month-1) AND (tonumber(strftime(ts,"%d"))<13)) OR ((tonumber(strftime(ts,"%m")) == now_month-2) AND (tonumber(strftime(ts,"%d"))>28)),duration_time))) AS res by milestone 
| dedup milestone 
| eval res=tostring(res,"duration") 
| table milestone res 

[chimell]

Hi

Try with If like below:

index=summary sourcetype=orc_srv | eval now_month = tonumber(strftime(now(),"%m")) | eval log_time_epoch = tonumber(strptime(log_time,"%Y-%m-%d %H:%M:%S"))
| eval ts = tonumber(strptime(Oracle_SeekTime,"%Y-%m-%d %H:%M:%S"))
| eval ts = log_time_epoch
| dedup ts 
|eval field1=if(((tonumber(strftime(ts,"%m")) == now_month-1)AND (tonumber(strftime(ts,"%d"))<13)) OR((tonumber(strftime(ts,"%m")) == now_month-2) AND (tonumber(strftime(ts,"%d"))>28)),duration_time)|stats sum(field1) as res by milestone
| dedup milestone 
| eval res=tostring(res,"duration") 
| table milestone res