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!
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
| eval category = case(match(host,"ABC-*"),"Staff",match(host,"DESKTOP*") OR match(host,"PC-*"),"Lab",true(),"Personal")
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