Splunk Search

Two stats on different period

tahasefiani
Explorer

Hello,
I have this query that return me the table below.

The query :

    | loadjob savedsearch="myquery"
          | where (strftime(_time, "%Y-%m-%d") >= "2019-12-09") AND (strftime(_time, "%Y-%m-%d") <= "2019-12-10")
          | stats dc(eval(if(((STEP=="Recieved",ID_MESSAGE,NULL))) AS Recieved,
                    dc(eval(if(((STEP=="answered",ID_MESSAGE,NULL))) AS answered,
                    dc(eval(if(((STEP=="deleted",ID_MESSAGE,NULL))) AS deleted by client
|fields client,Recieved,answered,deleted

Result :

alt text

The problem is , for some client like "35535" he receieved the Message 10/12 but he answered the 12/12, i want to give my client a margin of 4 days.
So,if my client recieves the message between the 09/12 and 10/12, i will check if his answered between the 09/10 and the 14/12.
the messages received and deleted must be over the period from 09/12 and 10/10, but those answered must be from 09/12 and 14/12
Thanks 🙂

0 Karma

to4kawa
Ultra Champion
| loadjob savedsearch="myquery" 
| stats dc(eval(if(STEP=="Recieved" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-10"),ID_MESSAGE,NULL))) AS Recieved,
    dc(eval(if(STEP=="answered" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-14",ID_MESSAGE,NULL))) AS answered,
    dc(eval(if(STEP=="deleted" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-10"),ID_MESSAGE,NULL))) AS deleted by client 
| fields client,Recieved,answered,deleted

Be careful time picker.

0 Karma

morethanyell
Builder

Not an answer but just wanted to point out that this portion | where (strftime(_time, "%Y-%m-%d") >= "2019-12-09") AND (strftime(_time, "%Y-%m-%d") <= "2019-12-10") might not work as expected. It's better to make comparison operation like greater than if the time is in epoch, rather than formatted in string. maybe consider doing |where _time >= strptime("2019-12-09", "%F") AND _time <= strptime("2019-12-10", "%F")

0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval check=if(strftime(now(),"%F") < strftime(relative_time(now(),"+1d@d"),"%F"), "OK" ,"NG")

I think so ,too. but Splunk can....

0 Karma

to4kawa
Ultra Champion

what's your expected result?

0 Karma

tahasefiani
Explorer

@to4kawa my expected result is :
client : 35535
Recieved : 1
answered : 1
deleted : 0
knowing that the research period is from 09/12 to 10/12,but because my client answered between 09 and 14 I display 1 in answered

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...