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 (1)
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, Karma 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, Karma would be appreciated.
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...