topic Re: Creating a column for each field in a count in Splunk Search

Creating a column for each field in a count

tpowell12 — Wed, 25 Jul 2012 18:44:29 GMT

I have a search for failed login attempts and am running a count based on EventCodes per host.

("EventCode=4625" OR "EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") | stats count by host, EventCode

host       EventCode    count  
host1        4625         3  
host1        529          6 
host2        529          3 
host3        529          6 
host3        4625         1

Instead of listing each host multiple times for each EventCode, how can I have the host listed once with each EventCode in its own column as I have below.

host        529         4625
host1        6            3
host2        3
host3        6            1

Re: Creating a column for each field in a count

dmaislin_splunk — Wed, 25 Jul 2012 19:31:30 GMT

Like this:

("EventCode=4625" OR "EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") | chart count(EventCode) over host by EventCode

Re: Creating a column for each field in a count

tpowell12 — Wed, 25 Jul 2012 19:50:25 GMT

Thanks for the quick response. Yeah, that solves it. Now from here, can I alias each of the EventCodes in the chart with something that I can understand rather than having the code number displayed?

Re: Creating a column for each field in a count

dmaislin_splunk — Wed, 25 Jul 2012 19:55:58 GMT

What you want to do is create a lookup table with Splunk and then you can do:

chart count(EventCode) over host by EventCodeDescription

Have you done a lookup table yet? If you are happy with my answer, please accept it.

Re: Creating a column for each field in a count

dmaislin_splunk — Wed, 25 Jul 2012 20:06:27 GMT

See this: http://docs.splunk.com/Documentation/Splunk/latest/User/CreateAndConfigureFieldLookups

Re: Creating a column for each field in a count

Gilberto_Castil — Wed, 25 Jul 2012 20:12:08 GMT

There is also an eventcode lookup in Splunkbase. Download it here: http://splunk-base.splunk.com/apps/22357/windows-event-codes-lookup.

Re: Creating a column for each field in a count

dmaislin_splunk — Wed, 25 Jul 2012 20:38:07 GMT

Good point Gilberto! And a big timesaver. You could download that app and then all the lookups would work assuming the field names for the lookup match to the field names he is using. An easy fix either way.