Reporting

Report Creation - Top 5 successful logins by user

skathpal
Explorer

Dear Experts,

I was trying to create the report of the top 5 successful login by user.
Query
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

markthompson
Builder

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

skathpal
Explorer

Thanks Mark

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

Tim 3 4 5

0 Karma

markthompson
Builder

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

satishsdange
Builder

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

0 Karma

markthompson
Builder

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!

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 ...

Adoption of Infrastructure Monitoring at Splunk

  Splunk's Growth Engineering team showcases one of their first Splunk product adoption-Splunk Infrastructure ...