How to match case on multiple value assigned

[aa0]

Hi all,

I'm trying to create category based on host category: Lab,Personal,Staff and get workstations to be counted for each category. I tried using below and it gives desired results however it doesn't work when I applied boolean expression (OR) on more details in certain category.

<base search>| eval category = case(match(host,"ABC-*"),"Staff",match(host,"DESKTOP*" OR host,"PC-*"),"Lab",true(),"Personal")|stats count by category,host|sort -count|stats sum(count) as Total list(host) as Workstation_Name list(count) as count by category|where Total>1|sort Total

Expected Result:

category | Total |     Workstation_Name     | count

    Staff          5                   ABC123                            2

                                               ABC345                           3

     Lab            2               DESKTOP123                     1

                                                   PC123                           1

     Personal   1                        Etc...                              1

Any help would be much appreciated!

[ITWhisperer]

| eval category = case(match(host,"ABC-*"),"Staff",match(host,"DESKTOP*") OR match(host,"PC-*"),"Lab",true(),"Personal")

[gcusello · Accepted solution]

Hi @aa0,

thesecond condition of the case is wrong and then there's the problem that the values in list are sorted by value, so they aren't aligned and you have to make a workaround:

<base search>
| eval category=case(match(host,"ABC-*"),"Staff",match(host,"DESKTOP*") OR match(host,"PC-*"),"Lab",true(),"Personal")
| stats values(category) AS category count by host
| eval column=host."|".count
| stats sum(count) as Total values(column) as column BY category
| where Total>1
| rex field=column "^(?< Workstation_Name>[^\|]+)\|(?<count>\d+)"
| sort Total
| table category Total Workstation_Name count

Ciao.

Giuseppe