How to showcase the count of devices and userlogged in?

[Nagalakshmi]

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

[Nagalakshmi]

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

[bowesmana]

Please give an example of your data

[Nagalakshmi]

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.

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

[bowesmana]

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"

[aromanauskas · Accepted solution]

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

[Nagalakshmi]

Thanks for your suggestions!!