How to calculate a score based on a field with different values and count the number of identical values by applying a multiplier?

[skhedim]

Hello,

I want to calculate a score based on a field (severity) containing different values (High, Medium, Low). This field is linked to a security scan on software.

I would like to count the number of identical values and apply a multiplier according to criticity) I get this table for a scan on a specific software

severity count
High 11
Low 8
Medium 14
Negligible 23
Unknown 1

I would like the count field values to be added in this way

Score = High*5 + Medium*4 + Low*3 + Negligible*2 + Unknown * 1

The result on this example would be 11*5 + 14*4 + 9*3 + 23*2 + 1*1 Score = 185

I tried with eval but I can't manage the conditions (High, medium,...)

Do you have any idea what to do?

Thank you in advance

Sincerely.

[skhedim]

Perfect ! Very effective, I did not know case it's very practical.
Thanks !

[damien_chillet · Accepted solution]

You could try append the following to your search:

| eval rate=case(
severity == "High", 5,
severity == "Medium", 4,
severity == "Low", 3,
severity == "Negligible", 2,
severity == "Unknown", 1)
| eval score=count*rate
| stats sum(score) as SCORE