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

[richardphung]

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!

[FrankVl · Accepted solution]

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

[richardphung]

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

[nikita_p]

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

[richardphung]

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"

[p_gurav]

Could you please provide sample results of tstats command?

[richardphung]

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