Splunk Search

Why is eval case not working in my search?

saurabhkunte
Path Finder

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 
Tags (3)
0 Karma

chimell
Motivator

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...