Report Creation - Top 5 successful logins by user


Dear Experts,

I was trying to create the report of the top 5 successful login by user.
index="wineventlog" sourcetype="wineventlog:security" "eventcode=4624" OR "eventcode=528" | top 5 user

Run above Simple query
Got the results
User Count
a 10
b 9
Now I want to see the log source from where user - a got the 10 count. I found host is the field where we can see the log source.

I need to write the query so that I can see same result including the field host(Log Source)

User Log_Source1 Log_Source2 Log_Source3 Total Count
a 4 4 2 10
b 5 3 1 9

Hope my question is clear

Thanks in advance

Tags (1)
0 Karma


Unfortunately you cannot use the top command to get values, for this you'd need to use head.

If your Count field exists simply;

index="wineventlog" sourcetype="wineventlog:security" "eventcode=4624" OR "eventcode=528" | sort +Count | table User, Count, Host |  head 5

If it doesn't exist, you might need to use the Stats function, and then use head.

Hope this helps

0 Karma


Thanks Mark

Above query return only one Column host , Actually I am looking for multiple columns of host from where got authenticated ,
User host1 host2 host3

Tim 3 4 5

0 Karma


Hi @skathpal - Can you please provide an example of these logs, so we can take a look, and are your fields already set up? I.e. Host1 host2 host3?

0 Karma


I see host1,host2,host3 are values in KV pair. You can create a chart that shows different values for these hosts.

0 Karma


Ah, so they already exist,
if you try this search string, what happens?:

 index="wineventlog" sourcetype="wineventlog:security" "eventcode=4624" OR "eventcode=528" | eval host1_count=mvcount(split(host1,"<WHATEVER DELIMITS VALUES>"))-1 | eval host2_count=mvcount(split(host2,"<WHATEVER DELIMITS VALUES>"))-1|  eval host3_count=mvcount(split(host3,"<WHATEVER DELIMITS VALUES>"))-1 | eval "Total Successes"=host1_count+host2_count+host3_count | sort +"Total Successes" | table User, "Total Successes", host1_count, host2_count, host3_count |  head 5

It's just an idea at the moment

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...