How to determine if a group is not used ?

[ckieken]

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 🙂

[adonio · Accepted solution]

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]

Awesome, very clear, thanks for your help !