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!

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 ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...