topic 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 in Splunk Search

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 — Mon, 20 Jan 2020 11:45:04 GMT

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

Re: 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

richgalloway — Mon, 20 Jan 2020 13:55:13 GMT

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.

Re: 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

somesoni2 — Mon, 20 Jan 2020 17:25:01 GMT

Try like this

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

Re: 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 — Tue, 21 Jan 2020 03:03:49 GMT

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.

Re: 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 — Tue, 21 Jan 2020 03:04:45 GMT

this worked for me. thanks alot!