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

niketnilay
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

niketnilay
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

niketnilay
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

niketnilay
Legend

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

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

niketnilay
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
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>