Hi,
i'm trying to calculate the average events weekly by their severity and comparing the daily amount with the weekly average, i created a multivalue field but the values in the field get reordered and they don't match the rest of the data (the severity multivalue field), I tried using mvsort() but it did not work, what did i do wrong? Thank you for any help. Query, results and expected results below:
index=myindex earliest=-7d@d latest=now()
| bin _time span=1d
| fields _time, severity
| stats count by _time, severity
| eventstats avg(count) as average by severity
| eval change_percent=round(((count-average)*100)/count,0)
| eval average=round(average,2)
| eval change_percent=change_percent+"%"
| table _time severity count average change_percent
| stats values(severity) as severity, values(count) as AlertCount, values(average) as average, values(change_percent) as change_percent by _time
| sort - _time
| eval average=mvsort(average)
| eval change_percent=mvsort(change_percent)
| eval AlertCount=mvsort(AlertCount)
| eval severity=mvsort(severity)
results:
| _time | severity | AlertCount | average | change_percent |
| 2022-12-23 | High Informational | 3 8 | 3.25 3.67 | -22% 59% |
| 2022-12-22 | High | 1 | 3.25 | -225% |
| 2022-12-21 | High Informational | 3 | 3.25 3.67 | -22% -8% |
| 2022-12-20 | High | 4 | 3.25 | 19% |
| 2022-12-19 | High Informational Medium | 1 2 5 | 2.00 3.25 3.67 | -100% -62% 27% |
expected results:
_time severity AlertCount average change_percent
| _time | severity | AlertCount | average | change_percent |
| 2022-12-23 | High Informational | 3 8 | 3.25 3.67 | -22% 59% |
| 2022-12-22 | High | 1 | 3.25 | -225% |
| 2022-12-21 | High Informational | 3 | 3.25 3.67 | -8% -22% |
| 2022-12-20 | High | 4 | 3.25 | 19% |
| 2022-12-19 | High Informational Medium | 1 2 5 | 3.25 3.67 2.00 | -225% -83,5% 60% |
