Splunk Search

Can you help me get the count by user from the following query?

Communicator

I am pulling information from the authentication datamodel by modifying the Excessive Failed Logins tstats command:

| tstats values(Authentication.tag) as "tag",values(Authentication.user) as "user",values(Authentication.dest) as "dest",count from datamodel="Authentication"."Authentication" where nodename="Authentication.Failed_Authentication"  by "Authentication.app","Authentication.src"  | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6

This provides a nice table with the app, src, tag, a multivalue field for users, a multivalue field for dest, and total counts.

I would like to get the count per user, but am unsure where to start.

I could possibly write a query directly against:

| from datamodel:"Authentication"."Failed_Authentication"

But it seems that doing it this way is rather resource intensive.

Any advice would be helpful.

Thanks!

0 Karma
1 Solution

Ultra Champion

If you want to get the count per user, then the key ingredient is to put by "Authentication.user" instead of by "Authentication.app","Authentication.src". Some further modification may be needed to get exactly what you need (e.g. remove the values(...) as user bit, rename Authentication.user to user etc.)

View solution in original post

0 Karma

Ultra Champion

If you want to get the count per user, then the key ingredient is to put by "Authentication.user" instead of by "Authentication.app","Authentication.src". Some further modification may be needed to get exactly what you need (e.g. remove the values(...) as user bit, rename Authentication.user to user etc.)

View solution in original post

0 Karma

Communicator

Yes, this is it.
Seems so obvious now. 😕

0 Karma

Contributor

Hi,
Is your datamodel accelerated? Because accelerated datamodel helps running faster searches and could you try below search?
| tstats values(Authentication.tag) as "tag",dc(Authentication.user) as "user",values(Authentication.dest) as "dest" from datamodel="Authentication"."Authentication" where nodename="Authentication.Failed_Authentication" by "Authentication.app","Authentication.src" | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6

0 Karma

Communicator

Yes, the datamodel is accelerated.

The search:

| tstats values(Authentication.tag) as "tag",dc(Authentication.user) as "user",values(Authentication.dest) as "dest" from datamodel="Authentication"."Authentication" where nodename="Authentication.Failed_Authentication" by "Authentication.app","Authentication.src" | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6

does not produce any results.

Although, with:

| tstats values(Authentication.tag) as "tag",dc(Authentication.user) as "user",values(Authentication.dest) as "dest",count from datamodel="Authentication"."Authentication" where nodename="Authentication.Failed_Authentication" by "Authentication.app","Authentication.src" | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6

I get a count of users as "user"

0 Karma

Champion

Could you please provide sample results of tstats command?

0 Karma

Communicator

app, src, tag, user, dest, count
win:remote, 123.456.789.012, authentication, username1, server01, 8
win:remote, 123.456.890.123, authentication, {username1, username2, username 3}, server02, 10
win:remote, 123.456.901.234, authentication, {username1, username3, username4}, server01, 5

0 Karma