Splunk Enterprise Security

Updating Timestamp in a Lookup Table

shayvdee
Explorer

Hi,
Trying to build a use case which looks at user logins and stores the Count, Earliest and Lastest times on a per user, per server basis. Eventually we want it to alert when a user logs in to a server for the first time.

Currently trying to do it through a lookup table that is appended every hour. The lookup table is created with the following:

| tstats summariesonly=true max(_time),min(_time), count from datamodel=WindowsEvents where EventID.EventCode=4624 NOT EventID.Logon_GUID="{00000000-0000-0000-0000-000000000000}" by host, EventID.Account_Name | outputlookup server_logins.csv

And then the alert is setup with the following query:

| inputlookup server_logins.csv | rename min(_time) as ftime | eval days_ago=((now()-ftime)/86400) | fields host, EventID.Account_Name, ftime, days_ago | where days_ago < 1

Can someone please advise me on the best way to append the lookup table with a new count and "latest" (or in this case max(_time)) if the record for the user/server already exists?

Ideally I would just like the user table to be appended every hour while storing the data of a years worth of logins.

Thanks in advance.
S.

0 Karma
1 Solution

jawaharas
Motivator

If you like add to events to existing lookup table, you can use append=T in the outputlookup comment as below.

| tstats summariesonly=true max(_time),min(_time), count from datamodel=WindowsEvents where EventID.EventCode=4624 NOT EventID.Logon_GUID="{00000000-0000-0000-0000-000000000000}" by host, EventID.Account_Name 
| outputlookup append=T server_logins.csv

Further, you can't update a particular row/field in a lookup table. You can either append events or overwrite entire lookup table. For more dynamic data, consider using KV Store lookup.

Reference:
https://docs.splunk.com/Documentation/Splunk/7.3.1/Knowledge/ConfigureKVstorelookups

View solution in original post

jawaharas
Motivator

If you like add to events to existing lookup table, you can use append=T in the outputlookup comment as below.

| tstats summariesonly=true max(_time),min(_time), count from datamodel=WindowsEvents where EventID.EventCode=4624 NOT EventID.Logon_GUID="{00000000-0000-0000-0000-000000000000}" by host, EventID.Account_Name 
| outputlookup append=T server_logins.csv

Further, you can't update a particular row/field in a lookup table. You can either append events or overwrite entire lookup table. For more dynamic data, consider using KV Store lookup.

Reference:
https://docs.splunk.com/Documentation/Splunk/7.3.1/Knowledge/ConfigureKVstorelookups

jawaharas
Motivator

@shayvdee
Please accept the answer if it significantly helped resolve your query for the benefit of other forum members, who might run into a similar issue.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...