How to edit my search to display users from my Windows logs that are not found in a lookup table?

[kaskirana01]

Hi,

I have my Windows logs with all users and I have a lookup which has few user names. I need to display the users which are not in the lookup table. Similar searches of mine works for all the cases, but in this case, it's giving a list of all users which are there in my Windows logs without doing the NOT.

My search is:

index=windows EventID=4624 OR EventID=540 OR EventID=528 |rex field=Username "\\\\(?.+)"
|search NOT [|inputlookup trusted_users.csv|table names|rename names as user]
|eval c_time=strftime(_time,"%m/%d/%y %H:%M:%S")
|stats values(users) by host

Someone please point out the error.
Thanks,
Kirana

[splunkton]

[|inputlookup trusted_users.csv|table names|rename names as user]

replace the names in the table statement with user 🙂

[kaskirana01]

This is also not working

[tormodbp · Accepted solution]

Hi,

You provide too little information for me to be sure to create a working solution for you, but I can give som feedback based on how I interpret the information supplied in the question.

If you want to filter out events that does not include the usernames you could use a query like this:

 index=windows EventID=4624 OR EventID=540 OR EventID=528  NOT [|inputlookup trusted_users.csv| fields + names]

The above inputlookup returns a list of the content of names-field, while the NOT notation would exclude all events containing one or more of the values in the names-field. The search assumes that the trusted users in the CSV-file are in the field names.

---

A possible implementation in your query would be something like this:

 index=windows EventID=4624 OR EventID=540 OR EventID=528 NOT [|inputlookup trusted_users.csv | fields + names]
 |eval c_time=strftime(_time,"%m/%d/%y %H:%M:%S")
 |stats values(user) by host

The search assumes that the trusted users in the CSV-file are in the field names.

Hope this helps!

Cheers,

[kaskirana01]

Hi,
The above query did not work.
I will explain the scenario.

1.I have my windows logs which has standard users and non standard users logged into the machine
2. I have to extract the users properly first since it is combined with host name. So I used rex command first.
3. Now I have a lookup table trusted_users.csv which has all the trusted users.

My result should contain all the users who are non standard users. i.e, the list of users who are not mentioned in the lookup table.

Can you please see now,

Thanks

[tormodbp]

Do you successfully extracted field for the user? Personally i have used "Field extraction" to extract all the important fields for my Windows logs. In theory the inputlookup will try to match any field.

I forgot to remove a word in the query. Please re-run the last query in my answer. You can also remove the eval and stats so that you can be sure that those are not causing the problem.

[kaskirana01]

Hi,
I have successfully extracted the field. But after the lookup, instead of displaying only the remaining users, the command is displaying all the users. 😞

[sundareshr]

Fieldname does not match. You have 3 instances where you use the user field.

1) rex command, I assume, its user
2)inputlookup, itsuser
3)statsitsusers`

[kaskirana01]

Yes. I have extracted the user using rex and comparing that user with the name field in trusted users.csv. In the stats(user) I am displaying it