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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...