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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...