create a table with one column will display some items on one field and another column to display all other items from the same field

[d4rk_sp1d3r]

I have firewall logs where the field "user" has multiple user id's including guest and unknown. I need to count all the events with user guest and unknown then create a table where one column will provide the result, name the column as unauthenticated . The other column should display all other users with the field name authenticated. Each row should also display the firewall name.

the table should display like this.
firewall |authenticated|unauthenticated
firewall1 | 100 | 35
firewall2 | 75 | 20
firewall3 | 65 |11

right now i can do this by doing 2 searches but it is displayed on 2 tables

ex. index=fw sourcetype=auth user=unknown AND user=guest | stats count by firewall
index=fw sourcetype=auth user!=unknown AND user!=guest | stats count by firewall

[somesoni2 · Accepted solution]

Try like this

 index=fw sourcetype=auth | eval type=if(user="unknown" OR user="guest", "unauthenticated", "authenticated") | chart count by firewall type

[d4rk_sp1d3r]

this worked for me. thanks alot!

[richgalloway]

The first query might have a typo in it. It's not possible for the 'user' field to be both "unknown" and "guest" at the same time.

[d4rk_sp1d3r]

that's not exactly the search i used so the spelling is not a concern. just want everyone to understand the question. the field user has different user id's in it. guest, unknown, user1, user2, so on and so fourth.