Splunk Search

How to have the Lookup command to output only the changes to a csv file?

neerajs_81
Contributor

Gentlemen, 

Need some help with lookup command.  i have a lookup table (csv) which is a master list of user accounts. It looks something like this.

user_id first last email phone manager
           



I have a Scheduled search that runs daily . This search  shows only the users that been modified , updated or newly created .

How can i append the results of this search to my above csv lookup file in such a way that it does not create duplicates ?   Basically  if the user record already exits in the csv and if the search finds one of his attributes has been updated ( for example: manager ),  then the outlookup should update the existing user record  in the csv rather than creating a duplicate one.  Hope i am clear.
I read some posts about users recommending   to use the below command, but don't understand how does appending solve this use case ?  Should i be using this ?

 

| append [inputlookup <lookup_csv>]

 


The "Scheduled Search"  is configured to "append"  to the csv lookup in its properties.  

neerajs_81_0-1647273464625.png

 

Thanks in advance

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Have your scheduled report "merge" the current contents of the csv file (using inputlookup as you suggest) with the new information such that it has the complete contents of the csv, then replace (rather than append) the results back to the lookup file.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

append can prevent losing user if user search doesn't return all existing users.  Another use of append is an intermediate step to determine whether a user is new by way of stats.  Similarly, you can use stats to find out whether a preexisting user is modified or unchanged.

However, if your goal is to update the CSV when user changes, you cannot use outputlookup to modify existing record unless you rewrite everything.  Per outputlookup,

An outputlookup search that is run with append=true might result in a situation where the lookup table or collection is only partially updated.

With append=false (default), you won't have duplicates just because the CSV already contains the user.

The following assumes that usersearch may not have all users.

 

source=mysource usersearch
| dedup user_id first last email phone manager ``` use latest ```
| append
  [ | inputlookup user_id lookup_csv
  | eval source="from_lookup" ]
| eventstats dc(source) as sourcecount by user_id
| where source=="mysource" OR isnull(sourcecount)
| table user_id first last email phone manager
| outputlookup lookup_csv

 

 

neerajs_81
Contributor

Thank you for the detailed information

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Have your scheduled report "merge" the current contents of the csv file (using inputlookup as you suggest) with the new information such that it has the complete contents of the csv, then replace (rather than append) the results back to the lookup file.

Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...