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 🙂
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
verify your lookup is there:
| inputlookup fruits.csv
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
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
verify your lookup is there:
| inputlookup fruits.csv
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
Awesome, very clear, thanks for your help !