Getting Data In

Problem of duplicate values

taha13
Explorer

Hello ,

I have a question (or a problem) about my code:

|loadjob savedsearch="a468413:ied:req_test2"
|eval time = strftime(_time,"%m/%d/%Y %I:%M %p")
|eval D1=if(MESSAGE="Connexion genesys OK + agent joint",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
|eval D2=if(MESSAGE="Proposition du questionnaire de satisfaction client",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
|eval diff=D2-D1
|where chatId="0007SaDCMCP301EX" AND (match(MESSAGE,"(\s|^)Replique(\s|$)") OR match(MESSAGE,"(\s|^)replique(\s|$)") OR MESSAGE="Proposition du questionnaire de satisfaction client" OR MESSAGE="Connexion genesys OK + agent joint") AND MESSAGE!="Le chat va etre cloture car le timeout de non replique client et conseiller a ete atteint"
|table _time,chatId,D1,D2
|sort _time asc

The reslt that i want,is to have a table with one line with colums (chatId,D1,D2 AND diff),so here i have two problems,i have three D1(i want to take just the fist one),the second problem is that i have a lot of lines of one chatID

alt text

Tags (2)
0 Karma
1 Solution

niketn
Legend

Try the following run anywhere search:

| makeresults
| eval chatId="004khjkdhsd76786",D1="1520048834.000"
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786"]
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786"]
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786",D2="1400050002.000"]
| sort - _time asc
| fillnull value=0 D1,D2
| dedup chatId D1 D2
| search NOT(D1=0 AND D2=0)
| table _time chatId D1 D2
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

Try the following run anywhere search:

| makeresults
| eval chatId="004khjkdhsd76786",D1="1520048834.000"
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786"]
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786"]
| append 
    [| makeresults
| eval chatId="004khjkdhsd76786",D2="1400050002.000"]
| sort - _time asc
| fillnull value=0 D1,D2
| dedup chatId D1 D2
| search NOT(D1=0 AND D2=0)
| table _time chatId D1 D2
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

taha13
Explorer

can I write the formula instead of the values?

0 Karma

niketn
Legend

@taha13, what do you mean by formula? In the above run anywhere example you need to replace commands from |makeresults to |eval chartId with your current search.

  |loadjob savedsearch="a468413:ied:req_test2"
  |eval time = strftime(_time,"%m/%d/%Y %I:%M %p")
  |eval D1=if(MESSAGE="Connexion genesys OK + agent joint",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
  |eval D2=if(MESSAGE="Proposition du questionnaire de satisfaction client",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
  |eval diff=D2-D1
  |where chatId="0007SaDCMCP301EX" AND (match(MESSAGE,"(\s|^)Replique(\s|$)") OR match(MESSAGE,"(\s|^)replique(\s|$)") OR MESSAGE="Proposition du questionnaire de satisfaction client" OR MESSAGE="Connexion genesys OK + agent joint") AND MESSAGE!="Le chat va etre cloture car le timeout de non replique client et conseiller a ete atteint"
 | sort - _time asc
 | fillnull value=0 D1,D2
 | dedup chatId D1 D2
 | search NOT(D1=0 AND D2=0)
 | table _time chatId D1 D2
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

taha13
Explorer

It's work,thank you 🙂

0 Karma

niketn
Legend

Glad it worked, do up-vote the comment/s that helped 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@taha13, can you try the following and confirm:

 |loadjob savedsearch="a468413:ied:req_test2"
 |eval time = strftime(_time,"%m/%d/%Y %I:%M %p")
 |eval D1=if(MESSAGE="Connexion genesys OK + agent joint",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
 |eval D2=if(MESSAGE="Proposition du questionnaire de satisfaction client",strptime(time, "%m/%d/%Y %I:%M %p") ,NULL)
 |eval diff=D2-D1
 |where chatId="0007SaDCMCP301EX" AND (match(MESSAGE,"(\s|^)Replique(\s|$)") OR match(MESSAGE,"(\s|^)replique(\s|$)") OR MESSAGE="Proposition du questionnaire de satisfaction client" OR MESSAGE="Connexion genesys OK + agent joint") AND MESSAGE!="Le chat va etre cloture car le timeout de non replique client et conseiller a ete atteint"
| sort _time asc
| dedup chatId D1 D2
| search D1=* OR D2=*
| table _time chatId D1 D2
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

taha13
Explorer

No result found

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...