topic Re: Can you help me get the count by user from the following query? in Splunk Search

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

richardphung — Mon, 07 Jan 2019 21:15:04 GMT

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!

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

p_gurav — Tue, 08 Jan 2019 07:04:49 GMT

Could you please provide sample results of tstats command?

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

nikita_p — Tue, 08 Jan 2019 08:10:22 GMT

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

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

FrankVl — Tue, 08 Jan 2019 08:50:10 GMT

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

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

richardphung — Tue, 08 Jan 2019 14:20:04 GMT

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

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

richardphung — Tue, 08 Jan 2019 14:23:51 GMT

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"

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

richardphung — Tue, 08 Jan 2019 14:31:53 GMT

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