In a search, how would I get the difference between the primary region and all other regions?

[kiamco]

I have this query that is supposed to get the difference between the primary region and all other regions, but for some reason nothing is being returned for d_*

   | eval ms_region=rtrim("region_"+ms_zone, "abcdefgh") 
    |chart count OVER tenant_id by ms_region
    |rename region_ap-southeast-1 as "primary_region"
    | rename region* as r*
    |foreach r* [eval d_<<MATCHSTR>>=primary_region - <<FIELD>>]

here is my table:

tenant_id   primary_region  r_ap-northeast-1    r_ap-south-1    r_us-east-1         r_us-west-1
18                 60         0                0                    0                     0
344              370          0                0                    0                     0
366             3505          0                23                   0                    0
441             1323          0                0                    0                       0   

My expected result would be to add columns like d_$region1$ d_$region2$, d_$region3$, which would contain the difference of the primary region and other regions.

I tried debugging it and found out, for some reason, <<FIELD>> in the foreach doesn't return anything.

[aholzer · Accepted solution]

You were almost there, just add single quotes (') around your <<FIELD>> reference and it should work as you expected:

| makeresults 
| eval primary_region = 60 
| eval r_ap-ne = 0 
| eval r_ap-s = 23 
| eval r_us-e = 0 
| eval r_us-w = 0
| foreach r* [ eval d<<MATCHSTR>> = primary_region - '<<FIELD>>' ]

This results in:

_time               d_ap-ne d_ap-s  d_us-e  d_us-w  primary_region  r_ap-ne r_ap-s  r_us-e  r_us-w
2018-11-29 13:35:31 60      37      60      60          60              0       23      0       0

Hope this helps

---EDIT---
The reason it fails in your search is because your field names have dashes (-) in them. When Splunk parses that out into the eval, the dash is treated as a mathematical minus so you get the equivalent of primary_region - 'r_ap' - 'northeast' - 1. And neither r_ap, nor northeast are fields that exist. By applying the single quotes, Splunk treats the entire string as a single field name.

[cmerriman]

you can review the foreach documentation here

[kiamco]

Thanks for pointing that out @aholzer , I have been trying to debug this query for hours and totally forgot that it needed a (')