Update CSV values without ovewriting old ones

[Sasquatchatmars]

Hi, 

I am trying to make a CSV table with users that have logged in the system. The CSV file contains a field "Time" and "Username".

1. Time = Last log in time of the user.

2. Username = The user that logged in.

The goal is the update the last log in time of each user without deleting the other ones. This is what happens now. 

Let's say the first input corresponds to the next example.

      Time       |        Username
   1601251200            User_Alpha
   1601254800            User_Bravo
                         User_Charlie
   1601265600            User_Delta

 What happens sometimes is that when I'm searching for new entries, it doesn't find log in timestamp for a user so it is left blank like this. 

      Time       |        Username
                         User_Alpha
                         User_Bravo
    1601272800           User_Charlie
    1601280000           User_Delta

The output that I would like is this 

      Time       |        Username
    1601251200           User_Alpha
    1601254800           User_Bravo
    1601272800           User_Charlie
    1601280000           User_Delta

Basically, what happened in the last example, it kept the content of example one and added the content of example 2. It updated the last login time from "User_Delta" with the last known log in time. 

I'm getting stuck at this level. Can anyone help me to find out how i do this? My whole search works, except for this part. 

Thanks anyway! 

[Sasquatchatmars]

Hello! Thank you for your reply, I tried what you said but it still overwrites the record with the latest results. For example, if a field in my file contains a result it overwrites it with a blank value if it didn't find anything. Even with the "override_if_empty=false" parameter set

[gcusello]

Hi @Sasquatchatmars,

let me understand, maybe I'm wrong:

• you want to store in a lookup all the Usernames that you find in the main search and the ones there are already stored in the lookup,
• Time must be the latest between search and lookup,
• if not present in search, it takes the lookup one;

is it correct?

Could you share the search you're using?

Ciao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello,

Indeed that is correct. 

index="wineventlog" source="WinEventLog:Security" action=success EventCode=4624 user=* 
| stats max(_time) as Time by user 
| append [| inputlookup account_status_tracker | fields Latest user]
| stats max(Time) as "Latest" by user
| table Latest user
| outputlookup account_status_tracker

This is the search I am using

[gcusello · Accepted solution]

Hi @Sasquatchatmars,

there's an error in your search: you have two names for the Time field before the second stats (Time and Lates), you should have the same field name:

index="wineventlog" source="WinEventLog:Security" action=success EventCode=4624 user=* 
| stats max(_time) as Latest by user 
| append [| inputlookup account_status_tracker | fields Latest user]
| stats max(Latest) as "Latest" by user
| table Latest user
| outputlookup account_status_tracker

Ciao.

Giuseppe

[Sasquatchatmars]

hi @gcusello 

This seems to be working! Thank you very much! I am going to test this in the couple of days. If there is anything wrong I'll come back. 

Have a good day,

Sasquatchatmars

[gcusello]

Hi @Sasquatchatmars,

good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

Hi @Sasquatchatmars,

you have to take records both from main search and lookup, something like this (I think that Username is a field both of the main search and lookup and Time is expressed in epochtime format, if not you have to rename one field and/or change format):

your_search
| rename _time AS Time
| append [ | inputlookup your_lookup.csv | fields Time Username ]
| stats latest(Time) AS Time BY Username
| table Time Username
| outputlookup your_lookup.csv

In this way you have the updated vale from the search and the old values from the lookup and the search takes the latest value.

Ciao.

Giuseppe