Splunk Search

How to Use an InputLookup File to provide authorized user list and Report back Users who are not in the inputlookup

deastman
SplunkTrust
SplunkTrust

I have an input lookup file. Say 'ApprovedUsers.csv'. This contains a single field SamAccountName. I want to compare this agains the Account_Name field returned in a Windows Security Eventlog search. I then want to compare the user who logged on per the log against the inputlookup file. If the User is NOT present in the lookup file, then I want it to fire an alert.

My problem is I cannot seem to get the search using a 'NOT' operation against the lookup file. But perhaps there is a way to achieve this type of outcome? I've also done a little reading about search macros? Would that be easier?

I'm open to alternative options or what is the best practice for this.

Thanks!
Dustin

0 Karma
1 Solution

deastman
SplunkTrust
SplunkTrust

I found an alternative method to resolve this by updating the search to simply return all of the results and just remove those with special characters. I chose this so that I did not have to modify the match_type= stanza for the default as this lookup was assigned to the default search app, and as of currently although I'm aware that to assign lookups to the default search app is not best practice this is the way we are required to do it.

index =myindexname = windows_logon_success|lookup LookupName Field OUTPUT Match| rename Field as user | search user!=$ user!=- user!=*App user!=Svc user!=SYSTEM|stats count by user| where count >= 1.

Perhaps this query could help someone else

View solution in original post

0 Karma

deastman
SplunkTrust
SplunkTrust

I found an alternative method to resolve this by updating the search to simply return all of the results and just remove those with special characters. I chose this so that I did not have to modify the match_type= stanza for the default as this lookup was assigned to the default search app, and as of currently although I'm aware that to assign lookups to the default search app is not best practice this is the way we are required to do it.

index =myindexname = windows_logon_success|lookup LookupName Field OUTPUT Match| rename Field as user | search user!=$ user!=- user!=*App user!=Svc user!=SYSTEM|stats count by user| where count >= 1.

Perhaps this query could help someone else

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi deastman,
the you can use the NOT option using the inputlookup command, e.g.:

 your_search NOT [ | inputlookup ApprovedUsers.csv | rename SamAccountName as Account_Name| fields Account_Name ]

the important thing is that the user field name must be the same both in search and in lookup.
in this way you can find the Account_Name in your search that aren't in your lookup.

If instead you want to find if some SamAccountName in your lookup is or not present in you search you can use this simple search:

your_search
| stats count by Account_Name
| append [ | inputlookup ApprovedUsers.csv | eval count=0 | rename SamAccountName as Account_Name| fields Account_Name count]
| stats sum(count) AS total by Account_Name
| where total=0

beware to upper or lower case of Account_Name because is relevant: to be more sure you could insert | eval Account_Name=upper(Account_Name) both in search and in subsearch.

Bye.
Giuseppe

0 Karma

deastman
SplunkTrust
SplunkTrust

Giuseppe,
First I want to thank you for your response! I did attempt this search, and the issue at hand is that it returns all of the entries in my csv file showing a count of 0 (indicating these users have not logged on). As soon as I then initiate a logon with one of the users, that entry is removed from the search (as would be expected).

However, what I'm trying to determine is when anyone EXCEPT the entries in the csv logon. I.e. If user abc is in file and logons that's fine. But then if user xyz logs on, and is not an entry in the CSV I need the search to return that user xyz logged on. And I need it to alert anytime a user NOT in the list logs on.

But right now I'm just focused on getting the search right, the alert portion is simple once I get the search to return properly. Any other suggestions?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi deastman,
To have this additional information you could run another simple query

your_search NOT [ | inputlookup ApprovedUsers.csv | rename SamAccountName as Account_Name| fields Account_Name ]
| dedup Account_Name
| table Account_Name

Eventually the two searches can be run in one search

 your_search
 | stats count by Account_Name
 | append [ | inputlookup ApprovedUsers.csv | eval count=0 | rename SamAccountName as Account_Name| fields Account_Name count]
 | stats sum(count) AS total by Account_Name
 | where total=0
 | eval Status "In lookup, not in logs"
 | append [
          your_search NOT [ | inputlookup ApprovedUsers.csv | rename SamAccountName as Account_Name| fields Account_Name ]
       | eval Status "In logs, not in lookup"
       | table _time Account_Name ]
 | table Account_Name Status

Bye.
Giuseppe

0 Karma

deastman
SplunkTrust
SplunkTrust

Giuseppe,
I think this query you have provided will definately provide what I"m looking for. However, In practice it is not working so I'm including my literal search string, modifying some specific names for anonymity

index = myindex eventtype = windows_logon_success user!=*$ user!=- user!=*App* user!=*Svc* user!=SYSTEM
| stats count by Account_Name
| append [ | inputlookup ApprovedUsers.csv | eval count=0 | rename SamAccountName as Account_Name| fields Account_Name count]
| stats sum(count) AS total by Account_Name
| where total=0
| eval Status "In lookup, not in logs"
| append [
search (index = myindex eventtype = windows_logon_success user!=*$ user!=- user!=*App* user!=*Svc* user!=SYSTEM) NOT [ | inputlookup ApprovedUsers.csv | rename SamAccountName as Account_Name| fields Account_Name ]
| eval Status "In logs, not in lookup"
| table _time Account_Name ]
| table Account_Name Status

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try this ...

your search that returns the Account_Name
| lookup ApprovedUsers.csv SamAccountName as Account_Name OUTPUT SamAccountName 
| where isnull(SamAccountName)
0 Karma

deastman
SplunkTrust
SplunkTrust

DalJeanis,
Thank you for your response. However, this search yielded no results at all. It did not return the users NOT in the list which logged on, but it also did not provide any information about the approved users.

But the goal of this is to look at the Approved user list and compare that to the 'successful logon events' and if the Account_Name is NOT in the list then I need it to count that or create it as a search result that I can count/return, and then use that to alert against.

Thanks!
Dustin

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...