topic Re: How to showcase the count of devices and userlogged in? in Splunk Search

How to showcase the count of devices and userlogged in?

Nagalakshmi — Wed, 23 Aug 2023 19:58:30 GMT

Hi Team,

We have users logging in multiple devices. So, we need to showcase the count of devices and user logged in. Can you please advise the query for same.

Regards,

Nagalakshmi A

Re: How to showcase the count of devices and userlogged in?

bowesmana — Thu, 24 Aug 2023 00:23:46 GMT

Please give an example of your data

Re: How to showcase the count of devices and userlogged in?

Nagalakshmi — Thu, 24 Aug 2023 18:03:38 GMT

Hi,

for example index=M365 type=logged in | stats count(username) as usernamecount by username,hostname. by this query we are getting expected results such as username,hostname.

however we need a field where system count can be shown for the userloggedIn.

By trying below command, we are getting the number of times users logged in to the systems, but we require system count a user logged in

index=M365 type=logged in | stats count(username) as usernamecount by username,hostname
| where usernamecount>1

Re: How to showcase the count of devices and userlogged in?

Nagalakshmi — Thu, 24 Aug 2023 18:47:25 GMT

Hi @bowesmana

for example index=M365 type=logged in | stats count(username) as usernamecount by username,hostname. by this query we are getting expected results such as username,hostname.

however we need a field where system count can be shown for the userloggedIn.

Re: How to showcase the count of devices and userlogged in?

aromanauskas — Fri, 25 Aug 2023 00:31:10 GMT

So it appears you want 2 counts? number of users per host and number of hosts per user.

Your search of:

index=M365 type=logged in
| stats count(username) as usernamecount by username,hostname

Gives just a count of the number of time the field username show up for events with a unique username and hostname combination.

If you want unique number of users per hostname..

| stats dc(username) AS unique_users, count(username) AS total_logins by hostname

Unique number of hosts per user..

| stats dc(hostname) AS unique_hosts, count(hostname) AS total_logins by username

Combining them is a little more difficult. You could try:

index=M365 type=logged in
| eventstats dc(hostname) AS host_count by username
| stats count(username) as usernamecount,max(host_count) AS host_count by username,hostname
| table username,hostname,unique_host_ct,unique_user_count

Re: How to showcase the count of devices and userlogged in?

bowesmana — Fri, 25 Aug 2023 00:35:23 GMT

Minor issue with the search is that the type=logged in is going to look for a field 'type' with a value of logged, and then the word in somewhere in the event. Maybe you mean

index=M365 type="logged in"

As for showing the count - do you mean you want to show the number of different users who have logged into the same hostname?

index=M365 type="logged in" | stats count as usernamecount by username, hostname | eventstats dc(username) as UniqueUsers by hostname

You don't need 'count(username)' which is counting the events that contain the username field for each user, but you are splitting by username, so its redundant. Just use "count"

Re: How to showcase the count of devices and userlogged in?

Nagalakshmi — Mon, 28 Aug 2023 10:39:22 GMT

Thanks for your suggestions!!