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.
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.
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.
you can review the foreach documentation here
Thanks for pointing that out @aholzer , I have been trying to debug this query for hours and totally forgot that it needed a (')