Problem of duplicate values

[taha13]

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

[niketn · Accepted solution]

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

[taha13]

can I write the formula instead of the values?

[niketn]

@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

[taha13]

It's work,thank you 🙂

[niketn]

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

[niketn]

@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

[taha13]

No result found