topic Re: How to count by command on repeating field values in event in Splunk Search

How to count by command on repeating field values in event

willemjongeneel — Wed, 10 Jul 2019 14:39:13 GMT

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

Re: How to count by command on repeating field values in event

woodcock — Wed, 10 Jul 2019 17:01:06 GMT

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

Re: How to count by command on repeating field values in event

willemjongeneel — Thu, 11 Jul 2019 08:53:02 GMT

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

Re: How to count by command on repeating field values in event

woodcock — Thu, 11 Jul 2019 14:46:45 GMT

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.

Re: How to count by command on repeating field values in event

willemjongeneel — Wed, 30 Sep 2020 01:18:44 GMT

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).

Re: How to count by command on repeating field values in event

willemjongeneel — Mon, 15 Jul 2019 07:17:14 GMT

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\"]]}}"

Re: How to count by command on repeating field values in event

woodcock — Mon, 15 Jul 2019 14:50:20 GMT

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

Re: How to count by command on repeating field values in event

willemjongeneel — Mon, 15 Jul 2019 17:32:46 GMT

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

Re: How to count by command on repeating field values in event

woodcock — Tue, 16 Jul 2019 01:40:44 GMT

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"

Re: How to count by command on repeating field values in event

willemjongeneel — Tue, 16 Jul 2019 07:41:22 GMT

Great, this worked!

Thank you for your help!

Kind regards,
Willem

Re: How to count by command on repeating field values in event

woodcock — Tue, 16 Jul 2019 09:40:25 GMT

Be sure to click Accept to close the quesgion!