How to Update a Lookup Table

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I'm using the query below to successfully create a 'lookup' table which contains the dates that user accounts were created or edited.

index=_audit action=edit_user operation=edit OR operation=create
|stats min(timestamp) as "created" by object
|rename object as user
|output lookup inactiveusers.csv

I know that data exists in the table because I have used the '| inputlookup mylookup' command.

I'm then trying to update this table via the following code:

index=_audit action=edit_user operation=edit OR operation=create
| stats min(timestamp) as "created" by object
| rename object as user
| lookup inactiveusers.csv user OUTPUT user AS exists
| where isnull(exists)
| fields – exists
| outputlookup append=t inactiveusers.csv

The problem I have is that when I run this query I receive the following error:

Could not append to file
'inactiveusers.csv': Cannot append to
file because none of the fields match.

I've looked a t quite a few tutorials and I thought I had followed them to the 'letter' but I've clearly done something wrong.

I just wondered whether someone may be able to look at this please and offer some guidance on where I've gone wrong.

Many thanks and kind regards

Chris

[jeffland]

I can't reproduce your error, the searches work fine for me. But the error is pretty clear, apparently there are fields in your search that differ from the fields in the lookup file. What do your results look like before the | outputlookup?

[IRHM73]

Hi @jeffland, thank you for taking the time to come back to me with this.

You will no doubt see by my reply to @DMohn, that his solution worked, but as you and @DMohn have stated, I'm still unsure why the original query didn't work. The only thing I can think of is that it's a bug in Splunk?

Kind Regards

Chris

[DMohn · Accepted solution]

I am not sure where the error in your query is, but have you tried combining inputlookup and outputlookup here?

 index=_audit action=edit_user operation=edit OR operation=create
 | stats min(timestamp) as "created" by object
 | rename object as user
 | inputlookup append=t inactiveusers.csv
 | sort -created
 | dedup user
 | outputlookup inactiveusers.csv

[IRHM73]

Hi @DMohn, thank you for this, your solution works because I created a new 'test user' and it extracted the correct information.

Although like, you, I'm still unsure where or what caused the initial problem.

Kind Regards

Chris