Splunk Search

How to differentiate events with Field values and group them by a different field?

maria2691
Path Finder

Hello Everyone

I have to differentiate few events with their field values.

In my events I have a field called EventCode. The events are about login information.
If the field value of EventCode is 1111, they are successful logins. If the values are 2222, they are failed logins.

I have another field host in my events.
The requirement is to diffentiate 1111 and 2222 from the EventCode and then show them as results for each host, how much is successful and how many have failed.

I need to make a column chart in which the X axis should be host and Y axis should be showing the graph for successful and failed logins separately for each host.

I am trying some basic commands, however I am unable to get the results exactly the way it is required.

Please help me on this regard!

Thanks
Maria Arokiaraj

0 Karma
1 Solution

chitturics
Explorer

EventCode=1111 OR EventCode=2222 |eval event_status=if(EventCode="1111","Success","Failed")|chart count over host by event_status

View solution in original post

maria2691
Path Finder

Hello @cmerriman

Below is a sample event which is a successful one. If the EventCode is 2222, it is to be considered as a failed one.

02/21/2018 01:12:43 PM
LogName=Microsoft*
SourceName=Microsoft*
EventCode=1111
EventType=4
Type=Information
ComputerName=abcd.local
User=NOT_TRANSLATED
Sid=S**
SidType=0
TaskCategory=The operation completed successfully.
OpCode=Info
RecordNumber=xxxx
Keywords=None
Message=Remote Desktop Services: User authentication succeeded:

User:
Domain:
Source Network Address: 1.1.1.1
Collapse
host = hostname1 source = WinEventLog:* sourcetype = WinEventLog*

Do I need to use the makeresults option for all the separate hosts, we have around 20. Is there any other way?
And also when I tried the sample code given by you, the counts I am getting is 1 for all the hosts and EventCodes even though the actual results are more than 600. Could you please check what is wrong with the code 😞

Thanks
Maria Arokiaraj

0 Karma

cmerriman
Super Champion

without knowing what your data looks like, this is what i've come up with using makeresults:

|makeresults |eval data="host=x,eventcode=1111 host=x,eventcode=2222 host=x,eventcode=1111 host=y,eventcode=2222 host=y,eventcode=2222 host=z,eventcode=1111 host=z,eventcode=1111 host=z,eventcode=1111"|makemv data|mvexpand data|rename data as _raw|kv|table host eventcode|eval{eventcode}=eventcode|fields - eventcode|stats count(*) as * by host

try using eval{EventCode}=EventCode to make a column for each code 1111 and 2222 and then count them by host. If this doesn't work, if you could provide some sample data and expected output, that would be helpful.

0 Karma

cmerriman
Super Champion

what is your search looking like? all of those fields are extracted? you have a field called EventCode and host? if so, all you should really need is something like |table host EventCode|eval{EventCode}=EventCode|fields - EventCode|stats count(*) as * by host

0 Karma

chitturics
Explorer

EventCode=1111 OR EventCode=2222 |eval event_status=if(EventCode="1111","Success","Failed")|chart count over host by event_status

View solution in original post

maria2691
Path Finder

Very simple. Pity I didn't even think of it 😞
Thanks @chitturics.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!