How to count by command on repeating field values in event

[willemjongeneel]

Good afternoon,

I have a question on a search. I have events in which there are several orders. Inside of the customers field there are orders that look like this: ["",""] --> ["3SYPFB009006802","3089"]. Within the ordernumber there also is a customercode. This is the part in the ordernumber behind the 3S that consists of letters. In example: 3SYPFB009006802 --> customercode = YPFB. What I want to achieve is do a count on the orders within the customers part grouped by customercode. Below you find three example events.

{"barcodes":{"ers":[],"ips":[],"customers":[["3SYPFB009006804","3089"],["3SYPFB009006802","3089"],["3SYPFB009006807","3089"]],"abs":[]}}
Show syntax highlighted

{"barcodes":{"ers":[],"ips":[],"customers":[["3SYQPU009165784","3085"],["3SYQPU009165819","3085"],["3SYQPU009165790","3085"]]}}
Show syntax highlighted

{"barcodes":{"ers":[],"ips":[],"customers":[["3SORG25943792","3085"],["3SORG25942905","3085"],["3SORG25942822","3085"]]}}
Show syntax highlighted

I have made a field extraction that extracts only the customers part: the values of the customers field look like this: 

["3SBUSD13339293","2929"],["3SBUSD13341744","2929"],["3SBUSD13341923","2929"]

After that I want to extract the seperate orders from the customers field. For this I use the search below:

index=index1

| rex field=customers max_match=0 "(?\[\"[A-Z|\d]{1,20}\"\,\"\d{4}\"\])" 

The values on the order field look like this: ["3SFTVC0014757","2285"]

From this field I want to extract the customercode. For this I use the search below:

index=index1

| rex field=customers max_match=0 "(?\[\"[A-Z|\d]{1,20}\"\,\"\d{4}\"\])" 
| rex field=order max_match=0 "\[\"[23]S(?[A-Z]{1,5})" 

From this, the values on the customercode field look like I would expect: for example: YPFB

Then when I try to do a count on orders by customercode, the count results in values that are way to high. I use the search below:

index=index1

| rex field=customers max_match=0 "(?\[\"[A-Z|\d]{1,20}\"\,\"\d{4}\"\])" 
| rex field=order max_match=0 "\[\"[23]S(?[A-Z]{1,5})"
| stats count(order) by customercode

If I do a count on the orders, I get a total of 10752. If I do the same count grouped by customercode, the sum of the values are way higher. One customer code for example has a count(order) value of 22231.

Do you have any idea what is causing the issue and if it is possible to fix this by changing my search?

Thanks, kind regards,
Willem Jongeneel

[woodcock · Accepted solution]

How about this:

| makeresults 
| eval raw="{\"barcodes\":{\"ers\":[[\"3SERS03668556\",\"9950\"],[\"3SERS03667778\",\"9950\"],[\"3SERS03666818\",\"9950\"],[\"3SERS03667300\",\"9950\"],[\"3SERS03667449\",\"9950\"],[\"3SERS03668284\",\"9950\"],[\"3SERS03667399\",\"9950\"],[\"3SERS03668210\",\"9950\"],[\"3SERS03668924\",\"9950\"],[\"3SERS03668642\",\"9950\"],[\"3SERS03668669\",\"9950\"],[\"3SERS03668585\",\"9950\"],[\"3SERS03668796\",\"9950\"],[\"3SERS03668834\",\"9950\"],[\"3SERS03668268\",\"9950\"],[\"3SERS03667620\",\"9950\"],[\"3SERS03668925\",\"9950\"],[\"3SERS03667742\",\"9950\"]],\"ips\":[],\"customers\":[[\"3STPWH732291032\",\"3085\"],[\"3STPWH838415231\",\"3085\"],[\"3STPWH826125705\",\"3085\"],[\"3STPWH773669416\",\"3085\"],[\"3STPWH841015805\",\"3085\"],[\"3STPWH033127663\",\"3085\"],[\"3STPWH663216254\",\"3085\"],[\"3STPWH8048381\",\"4944\"],[\"3STTZY000082868\",\"3085\"],[\"3STTZY000082870\",\"3085\"],[\"3STTZY000082869\",\"3085\"],[\"3SETAM003221384\",\"3085\"],[\"3SDKJA000045004\",\"4946\"],[\"3SDKJA000044983\",\"4946\"],[\"3SDKJA000044962\",\"3085\"],[\"3SDKJA000044933\",\"4946\"],[\"3SDKJA000044951\",\"3085\"],[\"3SDKJA000044997\",\"4946\"],[\"3SDKJA000045007\",\"4946\"],[\"3SDKJA000044904\",\"4946\"],[\"3SDKJA000044959\",\"3085\"],[\"3SETAM003221379\",\"3085\"],[\"3SHEUK00511962\",\"3085\"],[\"3SHEUK00511966\",\"3085\"],[\"3SHEUK00511964\",\"3085\"],[\"3SHEUK00511972\",\"3085\"],[\"3SHEUK00511967\",\"3085\"],[\"3SHEUK00511973\",\"3085\"],[\"3SHEDU10318238\",\"3085\"],[\"3SYTIE17462114\",\"3533\"],[\"3SYTIE17462165\",\"3085\"],[\"3SYTIE17460021\",\"3085\"],[\"3SHGBE000000938\",\"4946\"]],\"abs\":[[\"3SYTIE17462165\",\"3085\"],[\"3STPWH8048381\",\"4944\"],[\"3SYTIE17462114\",\"3533\"],[\"3SHGBE000000938\",\"4946\"],[\"3SYTIE17460021\",\"3085\"],[\"3STPWH826125705\",\"3085\"],[\"3STPWH773669416\",\"3085\"],[\"3STPWH732291032\",\"3085\"],[\"3STPWH841015805\",\"3085\"],[\"3STPWH838415231\",\"3085\"],[\"3STPWH033127663\",\"3085\"],[\"3STPWH663216254\",\"3085\"],[\"3SERS03668556\",\"9950\"],[\"3SERS03667778\",\"9950\"],[\"3SERS03666818\",\"9950\"],[\"3SERS03667300\",\"9950\"],[\"3SERS03667449\",\"9950\"],[\"3SERS03668284\",\"9950\"],[\"3SERS03667399\",\"9950\"],[\"3SERS03668210\",\"9950\"],[\"3SERS03668924\",\"9950\"],[\"3SERS03668642\",\"9950\"],[\"3SERS03668669\",\"9950\"],[\"3SERS03668585\",\"9950\"],[\"3SERS03668796\",\"9950\"],[\"3SERS03668834\",\"9950\"],[\"3SERS03668268\",\"9950\"],[\"3SERS03667620\",\"9950\"],[\"3SERS03668925\",\"9950\"],[\"3SERS03667742\",\"9950\"],[\"3STKQN00129430\",\"3189\"],[\"3STKQN00129431\",\"3189\"],[\"3SHEUK00511970\",\"3085\"],[\"3SHEUK00511969\",\"3085\"],[\"3SHEUK00511975\",\"3085\"],[\"3SHEUK00511976\",\"3085\"],[\"3SHEUK00511963\",\"3085\"],[\"3SHEUK00511977\",\"3085\"],[\"3SHEUK00511962\",\"3085\"],[\"3SHEUK00511966\",\"3085\"],[\"3SHEUK00511974\",\"3085\"],[\"3SHEUK00511965\",\"3085\"],[\"3SHEUK00511964\",\"3085\"],[\"3SHEUK00511968\",\"3085\"],[\"3SHEUK00511971\",\"3085\"],[\"3SHEUK00511972\",\"3085\"],[\"3SHEUK00511967\",\"3085\"],[\"3SHEUK00511973\",\"3085\"],[\"3SHEDU10318238\",\"3085\"]]}}:::{\"barcodes\":{\"ers\":[[\"3SERS03668450\",\"9950\"],[\"3SERS03667515\",\"9950\"],[\"3SERS03668573\",\"9950\"],[\"3SERS03668576\",\"9950\"],[\"3SERS03668802\",\"9950\"],[\"3SERS03668882\",\"9950\"]],\"ips\":[],\"customers\":[[\"3SYTIE17462113\",\"2928\"],[\"LX400221966NL\",\"9980\"],[\"LX400222025NL\",\"9980\"],[\"LX400221935NL\",\"9980\"],[\"LX400221881NL\",\"9980\"],[\"LX400221983NL\",\"9980\"],[\"LX400221970NL\",\"9980\"],[\"LX400221895NL\",\"9980\"],[\"LX400221918NL\",\"9980\"],[\"LX400221952NL\",\"9980\"],[\"LX400221997NL\",\"9980\"],[\"LX400222003NL\",\"9980\"],[\"LX400221949NL\",\"9980\"],[\"LX400221904NL\",\"9980\"],[\"LX400222017NL\",\"9980\"],[\"LX400221921NL\",\"9980\"],[\"3STFEG009021544\",\"3085\"],[\"3SYTIE17462170\",\"3533\"],[\"3STMLD004226307\",\"3791\"],[\"3STMLD004226308\",\"3791\"],[\"3SYTIE17462174\",\"3085\"],[\"3SYTIE17462146\",\"3085\"],[\"3SYTIE17462068\",\"3085\"],[\"3SYTIE17462076\",\"3085\"],[\"3SYTIE17462120\",\"3085\"],[\"3SYTIE17462133\",\"3085\"],[\"3SYTIE17462084\",\"3085\"],[\"3SYTIE17462171\",\"4940\"],[\"3STVVF000022929\",\"2929\"],[\"3STVVF000022927\",\"2929\"],[\"3STVVF000022966\",\"2929\"],[\"3STVVF000022961\",\"2929\"],[\"3STVVF000022943\",\"2929\"],[\"3STMLD004226309\",\"3790\"],[\"3SETAM003221388\",\"3085\"],[\"3SETAM003221386\",\"3085\"],[\"3SETAM003221389\",\"3085\"],[\"3SETAM003221383\",\"3085\"],[\"3SETAM003221381\",\"3085\"],[\"3STOWL009028771\",\"3089\"]],\"abs\":[[\"3SETAM003221384\",\"3085\"],[\"3SETAM003221388\",\"3085\"],[\"3SETAM003221379\",\"3085\"],[\"3SDKJA000044997\",\"4946\"],[\"3SDKJA000045004\",\"4946\"],[\"3SDKJA000044983\",\"4946\"],[\"3SDKJA000045007\",\"4946\"],[\"3SDKJA000044904\",\"4946\"],[\"3SDKJA000044962\",\"3085\"],[\"3SDKJA000044959\",\"3085\"],[\"3SDKJA000044933\",\"4946\"],[\"3SDKJA000044951\",\"3085\"],[\"3SETAM003221386\",\"3085\"],[\"3SETAM003221389\",\"3085\"],[\"3SETAM003221383\",\"3085\"],[\"3SETAM003221381\",\"3085\"],[\"3STOWL009028771\",\"3089\"],[\"3SYTIE17462113\",\"2928\"],[\"3STFEG009021544\",\"3085\"],[\"LX400221966NL\",\"9980\"],[\"LX400222025NL\",\"9980\"],[\"LX400221935NL\",\"9980\"],[\"LX400221881NL\",\"9980\"],[\"LX400221970NL\",\"9980\"],[\"LX400221918NL\",\"9980\"],[\"LX400221997NL\",\"9980\"],[\"LX400221904NL\",\"9980\"],[\"LX400222017NL\",\"9980\"],[\"LX400221983NL\",\"9980\"],[\"LX400221895NL\",\"9980\"],[\"LX400221952NL\",\"9980\"],[\"LX400222003NL\",\"9980\"],[\"LX400221949NL\",\"9980\"],[\"LX400221921NL\",\"9980\"],[\"3SYTIE17462170\",\"3533\"],[\"3STMLD004226307\",\"3791\"],[\"3STMLD004226308\",\"3791\"],[\"3SYTIE17462174\",\"3085\"],[\"3SYTIE17462146\",\"3085\"],[\"3SYTIE17462068\",\"3085\"],[\"3SYTIE17462076\",\"3085\"],[\"3SYTIE17462120\",\"3085\"],[\"3SYTIE17462133\",\"3085\"],[\"3SYTIE17462084\",\"3085\"],[\"3SYTIE17462171\",\"4940\"],[\"3SERS03668450\",\"9950\"],[\"3SERS03667515\",\"9950\"],[\"3SERS03668573\",\"9950\"],[\"3STTZY000082868\",\"3085\"],[\"3STTZY000082870\",\"3085\"],[\"3STTZY000082869\",\"3085\"],[\"3SERS03668576\",\"9950\"],[\"3SERS03668802\",\"9950\"],[\"3SERS03668882\",\"9950\"]]}}:::{\"barcodes\":{\"ers\":[],\"ips\":[],\"customers\":[[\"3SPROM003221376\",\"3085\"],[\"3SPROM003221382\",\"3085\"],[\"3SPROM003221378\",\"3085\"],[\"3SPROM003221380\",\"3085\"],[\"3SYEOU009068286\",\"3085\"],[\"3SYEOU009068287\",\"3085\"],[\"3SYEOU9068285\",\"4940\"],[\"3SDOKC000065672\",\"3189\"],[\"3SDOKC000065675\",\"3189\"],[\"3SDOKC000065670\",\"3189\"],[\"3SDOKC0188908\",\"4944\"],[\"3SDOKC000065669\",\"3085\"],[\"3SDOKC000065674\",\"3085\"],[\"3SDOKC0188909\",\"4944\"],[\"3SDOKC000065671\",\"3085\"],[\"3SDOKC000065673\",\"3085\"],[\"3SDOKC0188907\",\"4944\"],[\"3SYTIE17462150\",\"3085\"],[\"3SYTIE17462149\",\"2928\"],[\"3SYTIE17462147\",\"3085\"],[\"3SYTIE17462159\",\"3085\"],[\"3SYTIE17462152\",\"3085\"],[\"3SYTIE17462148\",\"3085\"],[\"3SYTIE17462157\",\"3085\"],[\"3SYTIE17462115\",\"3533\"],[\"3SCBLU3344225\",\"3089\"],[\"3SCBLU3344226\",\"3533\"],[\"3SCBLU3344223\",\"3085\"],[\"3SCBLU3344352\",\"3089\"],[\"3SCBLU3344354\",\"3089\"],[\"3SCBLU3344222\",\"3085\"],[\"3SCBLU3344356\",\"3089\"],[\"3SYTIE17462151\",\"3089\"],[\"3SCBRT3344350\",\"3240\"],[\"3SCBRT3344351\",\"3240\"],[\"3SCBRT3344487\",\"3240\"],[\"3SCBRT3344353\",\"3240\"],[\"3SCBRT3344529\",\"3240\"],[\"3SYEEZ000117428\",\"3085\"],[\"3STKQN00129430\",\"3189\"],[\"3STKQN00129431\",\"3189\"],[\"3SHEUK00511975\",\"3085\"],[\"3SHEUK00511974\",\"3085\"],[\"3SHEUK00511965\",\"3085\"],[\"3SHEUK00511968\",\"3085\"],[\"3SHEUK00511971\",\"3085\"],[\"3SHEUK00511970\",\"3085\"],[\"3SHEUK00511969\",\"3085\"],[\"3SHEUK00511976\",\"3085\"],[\"3SHEUK00511963\",\"3085\"],[\"3SHEUK00511977\",\"3085\"],[\"3STMLD004226303\",\"3653\"],[\"3STMLD004226304\",\"3653\"]],\"abs\":[[\"3SPROM003221376\",\"3085\"],[\"3SPROM003221382\",\"3085\"],[\"3SYEOU009068287\",\"3085\"],[\"3SYEOU009068286\",\"3085\"],[\"3SPROM003221378\",\"3085\"],[\"3SPROM003221380\",\"3085\"],[\"3SYEOU9068285\",\"4940\"],[\"3SDOKC000065675\",\"3189\"],[\"3SDOKC000065670\",\"3189\"],[\"3SDOKC000065672\",\"3189\"],[\"3SDOKC0188908\",\"4944\"],[\"3SDOKC000065669\",\"3085\"],[\"3SDOKC000065674\",\"3085\"],[\"3SDOKC0188909\",\"4944\"],[\"3SDOKC000065671\",\"3085\"],[\"3SDOKC000065673\",\"3085\"],[\"3SDOKC0188907\",\"4944\"],[\"3SYTIE17462150\",\"3085\"],[\"3SYTIE17462149\",\"2928\"],[\"3SYTIE17462147\",\"3085\"],[\"3SYTIE17462159\",\"3085\"],[\"3SYTIE17462152\",\"3085\"],[\"3SYTIE17462148\",\"3085\"],[\"3SYTIE17462157\",\"3085\"],[\"3SCBLU3344225\",\"3089\"],[\"3SCBLU3344226\",\"3533\"],[\"3SCBLU3344223\",\"3085\"],[\"3SCBLU3344352\",\"3089\"],[\"3SCBLU3344354\",\"3089\"],[\"3SCBLU3344222\",\"3085\"],[\"3SCBLU3344356\",\"3089\"],[\"3SYTIE17462115\",\"3533\"],[\"3SYTIE17462151\",\"3089\"],[\"3SCBRT3344529\",\"3240\"],[\"3SCBRT3344350\",\"3240\"],[\"3SCBRT3344351\",\"3240\"],[\"3SCBRT3344487\",\"3240\"],[\"3SCBRT3344353\",\"3240\"],[\"3SYEEZ000117428\",\"3085\"],[\"3SCAZL0020631\",\"3189\"],[\"3SCAZL0020644\",\"3189\"],[\"3SCAZL0020618\",\"3189\"],[\"3SCAZL0020683\",\"3189\"],[\"3SCAZL0020625\",\"3189\"],[\"3SCAZL0020670\",\"3189\"],[\"3STMLD004226304\",\"3653\"],[\"3STMLD004226303\",\"3653\"]]}}"
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| rex field=_raw ",\"customers\":\[(?<customers>.*?\])\](?:,|})" 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution" 

| rex field=customers max_match=0 "(?<order>\[\"[A-Z|\d]{1,20}\",\"\d{4}\"\])" 
| rex field=order max_match=0 "\[\"[23]S(?<customercode>[A-Z]{1,5})"
| rex field=order mode=sed "s/^(\[\")(.{2})(.{4})(.+?)\",\"(\d+)\"\]/customer_code=\3,cutomer_order=\2\3\4,store_order=\5/"
| table _time order
| mvexpand order
| rename order AS _raw
| kv
| stats count by customer_code
| addtotals row=f col=t
| fillnull value="TOTAL"

[willemjongeneel]

Great, this worked!

Thank you for your help!

Kind regards,
Willem

[woodcock]

Be sure to click Accept to close the quesgion!

[woodcock]

Try this:

| makeresults 
| eval raw="{\"barcodes\":{\"ers\":[],\"ips\":[],\"customers\":[[\"3SYPFB009006804\",\"3089\"],[\"3SYPFB009006802\",\"3089\"],[\"3SYPFB009006807\",\"3089\"]],\"abs\":[]}}:::{\"barcodes\":{\"ers\":[],\"ips\":[],\"customers\":[[\"3SYQPU009165784\",\"3085\"],[\"3SYQPU009165819\",\"3085\"],[\"3SYQPU009165790\",\"3085\"]]}}:::{\"barcodes\":{\"ers\":[],\"ips\":[],\"customers\":[[\"3SORG25943792\",\"3085\"],[\"3SORG25942905\",\"3085\"],[\"3SORG25942822\",\"3085\"]]}}" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| rex field=_raw ",\"customers\":\[(?<customers>.*?\])\](?:,|})"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=customers max_match=0 "(?<order>\[\"[A-Z|\d]{1,20}\",\"\d{4}\"\])" 
| rex field=order max_match=0 "\[\"[23]S(?<customercode>[A-Z]{1,5})"
| eval customercode=mvdedup(customercode)
| stats dc(order) by customercode

[willemjongeneel]

Hello,

Thank you for your response.

I've tried the suggestion above. I still see a big difference. When i count the orders, as compared to the sum of the count by customercode. When I count orders I count around 30.000 in an hour, the sum of the count by customercode is around 75.000.

Any idea on what might be wrong?

Thanks, kind regards,
Willem Jongeneel

[willemjongeneel]

In both queries I used the events below:

| eval raw="{\"barcodes\":{\"ers\":[[\"3SERS03668556\",\"9950\"],[\"3SERS03667778\",\"9950\"],[\"3SERS03666818\",\"9950\"],[\"3SERS03667300\",\"9950\"],[\"3SERS03667449\",\"9950\"],[\"3SERS03668284\",\"9950\"],[\"3SERS03667399\",\"9950\"],[\"3SERS03668210\",\"9950\"],[\"3SERS03668924\",\"9950\"],[\"3SERS03668642\",\"9950\"],[\"3SERS03668669\",\"9950\"],[\"3SERS03668585\",\"9950\"],[\"3SERS03668796\",\"9950\"],[\"3SERS03668834\",\"9950\"],[\"3SERS03668268\",\"9950\"],[\"3SERS03667620\",\"9950\"],[\"3SERS03668925\",\"9950\"],[\"3SERS03667742\",\"9950\"]],\"ips\":[],\"customers\":[[\"3STPWH732291032\",\"3085\"],[\"3STPWH838415231\",\"3085\"],[\"3STPWH826125705\",\"3085\"],[\"3STPWH773669416\",\"3085\"],[\"3STPWH841015805\",\"3085\"],[\"3STPWH033127663\",\"3085\"],[\"3STPWH663216254\",\"3085\"],[\"3STPWH8048381\",\"4944\"],[\"3STTZY000082868\",\"3085\"],[\"3STTZY000082870\",\"3085\"],[\"3STTZY000082869\",\"3085\"],[\"3SETAM003221384\",\"3085\"],[\"3SDKJA000045004\",\"4946\"],[\"3SDKJA000044983\",\"4946\"],[\"3SDKJA000044962\",\"3085\"],[\"3SDKJA000044933\",\"4946\"],[\"3SDKJA000044951\",\"3085\"],[\"3SDKJA000044997\",\"4946\"],[\"3SDKJA000045007\",\"4946\"],[\"3SDKJA000044904\",\"4946\"],[\"3SDKJA000044959\",\"3085\"],[\"3SETAM003221379\",\"3085\"],[\"3SHEUK00511962\",\"3085\"],[\"3SHEUK00511966\",\"3085\"],[\"3SHEUK00511964\",\"3085\"],[\"3SHEUK00511972\",\"3085\"],[\"3SHEUK00511967\",\"3085\"],[\"3SHEUK00511973\",\"3085\"],[\"3SHEDU10318238\",\"3085\"],[\"3SYTIE17462114\",\"3533\"],[\"3SYTIE17462165\",\"3085\"],[\"3SYTIE17460021\",\"3085\"],[\"3SHGBE000000938\",\"4946\"]],\"abs\":[[\"3SYTIE17462165\",\"3085\"],[\"3STPWH8048381\",\"4944\"],[\"3SYTIE17462114\",\"3533\"],[\"3SHGBE000000938\",\"4946\"],[\"3SYTIE17460021\",\"3085\"],[\"3STPWH826125705\",\"3085\"],[\"3STPWH773669416\",\"3085\"],[\"3STPWH732291032\",\"3085\"],[\"3STPWH841015805\",\"3085\"],[\"3STPWH838415231\",\"3085\"],[\"3STPWH033127663\",\"3085\"],[\"3STPWH663216254\",\"3085\"],[\"3SERS03668556\",\"9950\"],[\"3SERS03667778\",\"9950\"],[\"3SERS03666818\",\"9950\"],[\"3SERS03667300\",\"9950\"],[\"3SERS03667449\",\"9950\"],[\"3SERS03668284\",\"9950\"],[\"3SERS03667399\",\"9950\"],[\"3SERS03668210\",\"9950\"],[\"3SERS03668924\",\"9950\"],[\"3SERS03668642\",\"9950\"],[\"3SERS03668669\",\"9950\"],[\"3SERS03668585\",\"9950\"],[\"3SERS03668796\",\"9950\"],[\"3SERS03668834\",\"9950\"],[\"3SERS03668268\",\"9950\"],[\"3SERS03667620\",\"9950\"],[\"3SERS03668925\",\"9950\"],[\"3SERS03667742\",\"9950\"],[\"3STKQN00129430\",\"3189\"],[\"3STKQN00129431\",\"3189\"],[\"3SHEUK00511970\",\"3085\"],[\"3SHEUK00511969\",\"3085\"],[\"3SHEUK00511975\",\"3085\"],[\"3SHEUK00511976\",\"3085\"],[\"3SHEUK00511963\",\"3085\"],[\"3SHEUK00511977\",\"3085\"],[\"3SHEUK00511962\",\"3085\"],[\"3SHEUK00511966\",\"3085\"],[\"3SHEUK00511974\",\"3085\"],[\"3SHEUK00511965\",\"3085\"],[\"3SHEUK00511964\",\"3085\"],[\"3SHEUK00511968\",\"3085\"],[\"3SHEUK00511971\",\"3085\"],[\"3SHEUK00511972\",\"3085\"],[\"3SHEUK00511967\",\"3085\"],[\"3SHEUK00511973\",\"3085\"],[\"3SHEDU10318238\",\"3085\"]]}}:::{\"barcodes\":{\"ers\":[[\"3SERS03668450\",\"9950\"],[\"3SERS03667515\",\"9950\"],[\"3SERS03668573\",\"9950\"],[\"3SERS03668576\",\"9950\"],[\"3SERS03668802\",\"9950\"],[\"3SERS03668882\",\"9950\"]],\"ips\":[],\"customers\":[[\"3SYTIE17462113\",\"2928\"],[\"LX400221966NL\",\"9980\"],[\"LX400222025NL\",\"9980\"],[\"LX400221935NL\",\"9980\"],[\"LX400221881NL\",\"9980\"],[\"LX400221983NL\",\"9980\"],[\"LX400221970NL\",\"9980\"],[\"LX400221895NL\",\"9980\"],[\"LX400221918NL\",\"9980\"],[\"LX400221952NL\",\"9980\"],[\"LX400221997NL\",\"9980\"],[\"LX400222003NL\",\"9980\"],[\"LX400221949NL\",\"9980\"],[\"LX400221904NL\",\"9980\"],[\"LX400222017NL\",\"9980\"],[\"LX400221921NL\",\"9980\"],[\"3STFEG009021544\",\"3085\"],[\"3SYTIE17462170\",\"3533\"],[\"3STMLD004226307\",\"3791\"],[\"3STMLD004226308\",\"3791\"],[\"3SYTIE17462174\",\"3085\"],[\"3SYTIE17462146\",\"3085\"],[\"3SYTIE17462068\",\"3085\"],[\"3SYTIE17462076\",\"3085\"],[\"3SYTIE17462120\",\"3085\"],[\"3SYTIE17462133\",\"3085\"],[\"3SYTIE17462084\",\"3085\"],[\"3SYTIE17462171\",\"4940\"],[\"3STVVF000022929\",\"2929\"],[\"3STVVF000022927\",\"2929\"],[\"3STVVF000022966\",\"2929\"],[\"3STVVF000022961\",\"2929\"],[\"3STVVF000022943\",\"2929\"],[\"3STMLD004226309\",\"3790\"],[\"3SETAM003221388\",\"3085\"],[\"3SETAM003221386\",\"3085\"],[\"3SETAM003221389\",\"3085\"],[\"3SETAM003221383\",\"3085\"],[\"3SETAM003221381\",\"3085\"],[\"3STOWL009028771\",\"3089\"]],\"abs\":[[\"3SETAM003221384\",\"3085\"],[\"3SETAM003221388\",\"3085\"],[\"3SETAM003221379\",\"3085\"],[\"3SDKJA000044997\",\"4946\"],[\"3SDKJA000045004\",\"4946\"],[\"3SDKJA000044983\",\"4946\"],[\"3SDKJA000045007\",\"4946\"],[\"3SDKJA000044904\",\"4946\"],[\"3SDKJA000044962\",\"3085\"],[\"3SDKJA000044959\",\"3085\"],[\"3SDKJA000044933\",\"4946\"],[\"3SDKJA000044951\",\"3085\"],[\"3SETAM003221386\",\"3085\"],[\"3SETAM003221389\",\"3085\"],[\"3SETAM003221383\",\"3085\"],[\"3SETAM003221381\",\"3085\"],[\"3STOWL009028771\",\"3089\"],[\"3SYTIE17462113\",\"2928\"],[\"3STFEG009021544\",\"3085\"],[\"LX400221966NL\",\"9980\"],[\"LX400222025NL\",\"9980\"],[\"LX400221935NL\",\"9980\"],[\"LX400221881NL\",\"9980\"],[\"LX400221970NL\",\"9980\"],[\"LX400221918NL\",\"9980\"],[\"LX400221997NL\",\"9980\"],[\"LX400221904NL\",\"9980\"],[\"LX400222017NL\",\"9980\"],[\"LX400221983NL\",\"9980\"],[\"LX400221895NL\",\"9980\"],[\"LX400221952NL\",\"9980\"],[\"LX400222003NL\",\"9980\"],[\"LX400221949NL\",\"9980\"],[\"LX400221921NL\",\"9980\"],[\"3SYTIE17462170\",\"3533\"],[\"3STMLD004226307\",\"3791\"],[\"3STMLD004226308\",\"3791\"],[\"3SYTIE17462174\",\"3085\"],[\"3SYTIE17462146\",\"3085\"],[\"3SYTIE17462068\",\"3085\"],[\"3SYTIE17462076\",\"3085\"],[\"3SYTIE17462120\",\"3085\"],[\"3SYTIE17462133\",\"3085\"],[\"3SYTIE17462084\",\"3085\"],[\"3SYTIE17462171\",\"4940\"],[\"3SERS03668450\",\"9950\"],[\"3SERS03667515\",\"9950\"],[\"3SERS03668573\",\"9950\"],[\"3STTZY000082868\",\"3085\"],[\"3STTZY000082870\",\"3085\"],[\"3STTZY000082869\",\"3085\"],[\"3SERS03668576\",\"9950\"],[\"3SERS03668802\",\"9950\"],[\"3SERS03668882\",\"9950\"]]}}:::{\"barcodes\":{\"ers\":[],\"ips\":[],\"customers\":[[\"3SPROM003221376\",\"3085\"],[\"3SPROM003221382\",\"3085\"],[\"3SPROM003221378\",\"3085\"],[\"3SPROM003221380\",\"3085\"],[\"3SYEOU009068286\",\"3085\"],[\"3SYEOU009068287\",\"3085\"],[\"3SYEOU9068285\",\"4940\"],[\"3SDOKC000065672\",\"3189\"],[\"3SDOKC000065675\",\"3189\"],[\"3SDOKC000065670\",\"3189\"],[\"3SDOKC0188908\",\"4944\"],[\"3SDOKC000065669\",\"3085\"],[\"3SDOKC000065674\",\"3085\"],[\"3SDOKC0188909\",\"4944\"],[\"3SDOKC000065671\",\"3085\"],[\"3SDOKC000065673\",\"3085\"],[\"3SDOKC0188907\",\"4944\"],[\"3SYTIE17462150\",\"3085\"],[\"3SYTIE17462149\",\"2928\"],[\"3SYTIE17462147\",\"3085\"],[\"3SYTIE17462159\",\"3085\"],[\"3SYTIE17462152\",\"3085\"],[\"3SYTIE17462148\",\"3085\"],[\"3SYTIE17462157\",\"3085\"],[\"3SYTIE17462115\",\"3533\"],[\"3SCBLU3344225\",\"3089\"],[\"3SCBLU3344226\",\"3533\"],[\"3SCBLU3344223\",\"3085\"],[\"3SCBLU3344352\",\"3089\"],[\"3SCBLU3344354\",\"3089\"],[\"3SCBLU3344222\",\"3085\"],[\"3SCBLU3344356\",\"3089\"],[\"3SYTIE17462151\",\"3089\"],[\"3SCBRT3344350\",\"3240\"],[\"3SCBRT3344351\",\"3240\"],[\"3SCBRT3344487\",\"3240\"],[\"3SCBRT3344353\",\"3240\"],[\"3SCBRT3344529\",\"3240\"],[\"3SYEEZ000117428\",\"3085\"],[\"3STKQN00129430\",\"3189\"],[\"3STKQN00129431\",\"3189\"],[\"3SHEUK00511975\",\"3085\"],[\"3SHEUK00511974\",\"3085\"],[\"3SHEUK00511965\",\"3085\"],[\"3SHEUK00511968\",\"3085\"],[\"3SHEUK00511971\",\"3085\"],[\"3SHEUK00511970\",\"3085\"],[\"3SHEUK00511969\",\"3085\"],[\"3SHEUK00511976\",\"3085\"],[\"3SHEUK00511963\",\"3085\"],[\"3SHEUK00511977\",\"3085\"],[\"3STMLD004226303\",\"3653\"],[\"3STMLD004226304\",\"3653\"]],\"abs\":[[\"3SPROM003221376\",\"3085\"],[\"3SPROM003221382\",\"3085\"],[\"3SYEOU009068287\",\"3085\"],[\"3SYEOU009068286\",\"3085\"],[\"3SPROM003221378\",\"3085\"],[\"3SPROM003221380\",\"3085\"],[\"3SYEOU9068285\",\"4940\"],[\"3SDOKC000065675\",\"3189\"],[\"3SDOKC000065670\",\"3189\"],[\"3SDOKC000065672\",\"3189\"],[\"3SDOKC0188908\",\"4944\"],[\"3SDOKC000065669\",\"3085\"],[\"3SDOKC000065674\",\"3085\"],[\"3SDOKC0188909\",\"4944\"],[\"3SDOKC000065671\",\"3085\"],[\"3SDOKC000065673\",\"3085\"],[\"3SDOKC0188907\",\"4944\"],[\"3SYTIE17462150\",\"3085\"],[\"3SYTIE17462149\",\"2928\"],[\"3SYTIE17462147\",\"3085\"],[\"3SYTIE17462159\",\"3085\"],[\"3SYTIE17462152\",\"3085\"],[\"3SYTIE17462148\",\"3085\"],[\"3SYTIE17462157\",\"3085\"],[\"3SCBLU3344225\",\"3089\"],[\"3SCBLU3344226\",\"3533\"],[\"3SCBLU3344223\",\"3085\"],[\"3SCBLU3344352\",\"3089\"],[\"3SCBLU3344354\",\"3089\"],[\"3SCBLU3344222\",\"3085\"],[\"3SCBLU3344356\",\"3089\"],[\"3SYTIE17462115\",\"3533\"],[\"3SYTIE17462151\",\"3089\"],[\"3SCBRT3344529\",\"3240\"],[\"3SCBRT3344350\",\"3240\"],[\"3SCBRT3344351\",\"3240\"],[\"3SCBRT3344487\",\"3240\"],[\"3SCBRT3344353\",\"3240\"],[\"3SYEEZ000117428\",\"3085\"],[\"3SCAZL0020631\",\"3189\"],[\"3SCAZL0020644\",\"3189\"],[\"3SCAZL0020618\",\"3189\"],[\"3SCAZL0020683\",\"3189\"],[\"3SCAZL0020625\",\"3189\"],[\"3SCAZL0020670\",\"3189\"],[\"3STMLD004226304\",\"3653\"],[\"3STMLD004226303\",\"3653\"]]}}"

[woodcock]

OK, so what are you getting and what do you think the correct answer is?

[willemjongeneel]

Hello,

Within the customers part there are 126 orders. Each order has one customercode. If I would count the orders by customercode, I would expect that the values would add up to 126 aswell. But this does not seem to happen as the sum of customercode values is 1034.

Kind regards,
Willem

[woodcock]

My code does the right thing according to the events that you provided. You are going to have to provide a set of events where it does not work.

[willemjongeneel]

Hello,

Below you find three sample events. When I count on ordernumbers (bcode) by using the search below I get 126 results for the customers part:

| makeresults
| eval raw=""
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex field=_raw ",\"customers\":[(?.*?])](?:,|})"
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| rex max_match=0 field=customers "\"(?[^\"]+)\",\"(?\d+)"
| stats count(bcode)

When I use the count by order query, the sum of the values is alot higher than 126 (1034).

| makeresults
| eval raw=""
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex field=_raw ",\"customers\":[(?.*?])](?:,|})"
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| rex field=customers max_match=0 "(?[\"[A-Z|\d]{1,20}\",\"\d{4}\"])"
| rex field=order max_match=0 "[\"[23]S(?[A-Z]{1,5})"
| eval customercode=mvdedup(customercode)
| stats dc(order) by customercode

I'll add the events in another comment (insufficient characters left).