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?

Makinde
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

javiergn
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

javiergn
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
0 Karma

Makinde
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

javiergn
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

Makinde
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

javiergn
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

Makinde
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...