How to count how many "200" codes are in my results, when using the mvjoin function?

[Abarny]

Hi guys can you tell me how i can count how many code 200 I have when i have do a mvjoin? I try with this search but it is not okay :

|stats list(ACR) as ACResponse by OCId 
|eval X=mvjoin(ACResponse,";") 
| search list(ACR)="*200*"
| stats count  list(ACR) by OCId

[DalJeanis · Accepted solution]

Depends on what you want the output to look like. This first part generates your test data, to the point of your first line:

| makeresults 
| eval mydata="200,487 487,487,487,487 200 603,487,487,487,200 200"
| makemv mydata 
| mvexpand mydata 
| makemv delim="," mydata
| rename mydata as ACResponse
| streamstats count as OCId
| table _time OCId ACResponse 

This part eliminates the records that do not include a 200 -

| search ACResponse=200

You could just do a stats count there, but since you seem to want to see the records, this part will collapse them and zip them together, then give you the count and list.

| eval ACResponse=mvjoin(ACResponse,";")
| eval OCId=mvzip(OCId,ACResponse,"::") 
| stats count list(OCId) as OCId

Resulting in this output

count  OCId
  4     1::200;487
        3::200
        4::603;487;487;487;200
        5::200

[Abarny]

I have just an other question, do you know how i can to do an average enter the max JourP and number where I have the ACResponse 200
(Ex: Moyen= AppelID/max(JourP) where AppelId is 3 and JourP is 5)

I do that :

|table OCId ACResponse date_wday
| stats dc(date_wday) as JourP count(ACResponse) as AppelD by ACResponse
| table JourP,AppelD,ACResponse

Thanks for your help !!

[DalJeanis]

That's a completely new question, so it's best to post it into the forum as such.

However, here's the answer, this time.

| table OCId ACResponse date_wday
| stats dc(date_wday) as JourP, count as AppelD by ACResponse 
| table JourP,AppelD,ACResponse
| eval Moyen= AppelID/JourP

That gets you the average number of events per day with any particular ACResponse, if I read it correctly. The following would give you the average of events per day with any particular ACResponse, across the entire period, assuming that at least one kind of ACResponse could be assured of happening every day. Use this code if (A) you know that one particular code will happen at least once every workday, and (B) there are non-workdays in the period in question that you don't want to diminish the average.

| table OCId ACResponse date_wday
| stats dc(date_wday) as JourP, count as AppelD by ACResponse 
| table JourP,AppelD,ACResponse
| eventstats max(JourP) as maxJourP
| eval Moyen=AppelID/maxJourP

More often, you will have a situation where you can't be sure that one ACResponse would be certain to happen every workday, and there are no days you want to exclude when calculating the average. Then you'd do something like this -

| table OCId ACResponse date_wday
| stats min(date_wday) as mindate, max(date_wday) as maxdate, dc(date_wday) as JourP1, count as AppelD by ACResponse 
| eventstats min(mindate) as mindate, max(maxdate) as maxdate
| eval  JourP2=round((maxdate-mindate)/86400,0)
| eval Moyen1= AppelID/JourP  
| eval Moyen2= AppelID/JourP2
| table ACResponse, AppelD, JourP1, Moyen1, JourP2, Moyen2

You can look at both the answers that come out of this and determine for yourself which one is more reasonable to your data.

[Abarny]

Thanks for your help !

[Abarny]

Thanks, your last solution is exactly what i wanted.

[DalJeanis]

You're welcome! Feel free to change the "::" delimiter to anything you like, it just seemed readable to me.