Splunk Search

How to determine if a group is not used ?

ckieken
Engager

Hi all,

Here is my problem: on the one hand, I have a lookup which is a list of group names.
On the other hand, I have logs generated when someone establishes a connexion to my bastion, and the logs give me the user's group.
I want to determine if some of my groups are useless (therefore never used).
So I am trying to join my research with my lookup, and I want to see which lines of the lookup has no match.

Here is my command :

sourcetype="bastion" | stats count by user_group | lookup Group_Names.csv "Group" as "user_group" OUTPUT "Group" | fillnull count

But as a result, I only get the lines that actually matched with a log.

Am I taking this problem the right way? Is it even possible to do this?

Thanks for your help 🙂

0 Karma
1 Solution

adonio
Ultra Champion

try this:

sourcetype="bastion" | stats count by user_group | rename user_group as Group | search Group=*  NOT [| inputlookup Group_Names.csv | fields Group | table Group ]

for example, try these steps:
1. create a lookup with some fruits

| makeresults count=1
| eval fruits = "apple,banana,orange,lemon"
| makemv delim="," fruits
| mvexpand fruits
| outputlookup fruits.csv
  1. verify your lookup is there:

    | inputlookup fruits.csv

  2. create more fruits and verify only the extra fruits are returned:

    | makeresults count=1
    | eval fruits = "apple,banana,orange,lemon,melon,watermelon"
    | makemv delim="," fruits
    | mvexpand fruits
    | search fruits = * NOT [| inputlookup fruits.csv | fields fruits | dedup fruits | table fruits ]

you supposed to see only melon and watermelon

hope it helps

View solution in original post

adonio
Ultra Champion

try this:

sourcetype="bastion" | stats count by user_group | rename user_group as Group | search Group=*  NOT [| inputlookup Group_Names.csv | fields Group | table Group ]

for example, try these steps:
1. create a lookup with some fruits

| makeresults count=1
| eval fruits = "apple,banana,orange,lemon"
| makemv delim="," fruits
| mvexpand fruits
| outputlookup fruits.csv
  1. verify your lookup is there:

    | inputlookup fruits.csv

  2. create more fruits and verify only the extra fruits are returned:

    | makeresults count=1
    | eval fruits = "apple,banana,orange,lemon,melon,watermelon"
    | makemv delim="," fruits
    | mvexpand fruits
    | search fruits = * NOT [| inputlookup fruits.csv | fields fruits | dedup fruits | table fruits ]

you supposed to see only melon and watermelon

hope it helps

ckieken
Engager

Awesome, very clear, thanks for your help !

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...