Splunk Search

How to edit my search to only display users that have logged on to more than 5 workstations and sort the list in descending order?

New Member

Hello,

Like the title says, I have the search criteria pretty nailed down, however, I would like to do a count so only events that match the count shows up in my report.

Here is the search string:

... search string .... | dedup user Workstation_Name | stats list(Workstation_Name) by user

This search string displays list of workstations each user has logged on to. However, I want to set a where condition where it only displays a user that has logged on to more than 5 different workstations as well as sort it such that it displays the users that have logged on to the most workstations.

How can I do this?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Another suggestion to avoid using dedup when not needed:

... search string .... 
| stats dc(Workstation_Name) as count by user
| where count > 5
| sort - count

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Another suggestion to avoid using dedup when not needed:

... search string .... 
| stats dc(Workstation_Name) as count by user
| where count > 5
| sort - count

View solution in original post

0 Karma

New Member

Thanks Javiergn,

I think I got that part nailed down, I have a new challenge now,

I would like to lookup a user against a lookup table, where it will exclude the a user account if it matches a list of workstation. The idea is we have some service accounts that are used on certain workstations I am think putting those workstations against the respective accounts in a lookup table and having splunk exclude users based on that list would be ideal. Is that even possible?

Secondly, even though I set the count to greater than 5, can specify a different count for a different type of user, so say for administrative accounts, only display results greater than 10.

Please let me know if this is possible, if you have an idea of another way to do this I would appreciate it as well.

Thanks,

0 Karma

SplunkTrust
SplunkTrust

Your second request is easy:

... search string .... 
 | eval user_type = case(user=="foo", "admin", user=="bar", "non-admin", 1==1, "others")
 | stats dc(Workstation_Name) as count by user, user_type
 | search (user_type = "admin" count > 10) OR (user_type != "admin" count > 5)
 | sort - count

With regards to your first request, a lookup should work great for this. Simply match against your lookup table and then exclude those events with a match. For example, the following query will match against a lookup table that returns for a given user id, the corresponding user name and then filters to only display those user ids without a user name:

your search here
| lookup userid OUTPUT username
| where isnull(username)

Take a look at the lookup reference which is very detailed.

0 Karma

New Member

Hi Javiergn,

Let's start with the second request, I tried it but no luck. Let's go thru this line again, as I think the problem is somewhere there;

| eval user_type = case(user=="foo", "admin", user=="bar", "non-admin", 1==1, "others")
What does this line mean? why the "==" I would have thought it should be just "=".
Why are we defining non-admin if we aren't using it in the search?

Thanks

0 Karma

SplunkTrust
SplunkTrust

Hi,

This is just an example in order to show you how to use the eval case statement to specify a different count for a different type of user (admin and non-admin in this case).

You have to use "==" in order to compare (equals to) variable with value. You could use match(variable, "string") if you wanted to do a partial matching. Take a look at the docs here.
The 1==1 is just a default condition in case nothing else matches and it's just to make sure all your possible values are captured.

Hope that makes sense.

0 Karma

New Member

I think I figured it out, I used the count filed against the user after the dedup. seems to be working.

Any other ideas will be welcomed.

0 Karma