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!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...