Splunk Enterprise Security
Highlighted

Updating Timestamp in a Lookup Table

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.LogonGUID="{00000000-0000-0000-0000-000000000000}" by host, EventID.AccountName | outputlookup server_logins.csv

And then the alert is setup with the following query:

| inputlookup serverlogins.csv | rename min(time) as ftime | eval daysago=((now()-ftime)/86400) | fields host, EventID.AccountName, ftime, daysago | where daysago < 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
Highlighted

Re: Updating Timestamp in a Lookup Table

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

Highlighted

Re: Updating Timestamp in a Lookup Table

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