Splunk Search

Inputlookup command not working

Sasquatchatmars
Communicator

Hi all,

I have been trying to use a search in order to compare two results. One is my lookup and one with an ldapsearch. I am trying to only keep the records of users who are actually still in the AD.

So my lookup contains the usernames and latest login time and my ldapsearch obviously has the updated list of every account still in the AD. 

My goal is to crosscheck if there are rows in the lookup that could be delete which is the goal of my query. So far I have the following but I am unable to append the lookup file. Can anyone help me achieve my goal? Tell me if this is the best way to do it and otherwise help me? If it this the best way can you correct my search? 

 

| ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName, distinguishedName" 
| append 
    [| inputlookup account_status_tracker | fields Latest, user] 
| eval match = if(user==sAMAccountName, "MATCH", "NOMATCH")
| table _time sAMAccountName Latest user match

 

 The table displays the values related to the ldapsearch but not the ones of the lookup file. 

Thanks anyway,

Sasquatchatmars

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The append command adds rows to your output rather than columns (that would be appendcols, but don't use that here).  Appended rows often need to be combined with earlier rows.  We can use stats to do that.

The eval command only looks at a single event so anything it compares must be in that one event.  In the example, only events containing both a user and a sAMAccountName field (which should be none of them) will have "MATCH".

| ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName, distinguishedName" 
| rename sAMAccountName as user
| append 
    [| inputlookup account_status_tracker | fields Latest, user] 
| stats values(*) as * by user
```Now the ldapsearch output is matched up with the inputlookup```
```Any row without a distinguishedName field didn't have an entry in ```
```ldapsearch so we can drop it.```
| where isnotnull(distinguishedName)
```Replace the table command with outputlookup to save the results in the lookup file```
| table Latest user
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Sasquatchatmars
Communicator

Hi @richgalloway ,

This worked like a charm, thank you very much! 

Sasquatchatmars

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The append command adds rows to your output rather than columns (that would be appendcols, but don't use that here).  Appended rows often need to be combined with earlier rows.  We can use stats to do that.

The eval command only looks at a single event so anything it compares must be in that one event.  In the example, only events containing both a user and a sAMAccountName field (which should be none of them) will have "MATCH".

| ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName, distinguishedName" 
| rename sAMAccountName as user
| append 
    [| inputlookup account_status_tracker | fields Latest, user] 
| stats values(*) as * by user
```Now the ldapsearch output is matched up with the inputlookup```
```Any row without a distinguishedName field didn't have an entry in ```
```ldapsearch so we can drop it.```
| where isnotnull(distinguishedName)
```Replace the table command with outputlookup to save the results in the lookup file```
| table Latest user
---
If this reply helps you, an upvote would be appreciated.

View solution in original post