Splunk Search

Listing hosts that haven't been locked for 24 hours

mbrownec
Explorer

I'm new to splunk, and logical switch statements have me a bit confused.

I'd like to produce a list of hosts that haven't been locked for 24 hours.

I came across a previous answer (by the name of "Find which hosts a user has not logged in to") that uses eval for this purpose, but I am seeing results where I know the EventCode has been logged

sourcetype="WinEventLog:Security" | stats count(eval(EventCode=4800)) as LockedCount by host | where LockedCount=0

If I walk through this query:

  • Per host, count all the records where eventcode=4800.
  • If there are no records counted, list the host.

As stated earlier, hosts where EventCode=4800 are returned with the above query. What am I misunderstanding?

Thanks!

Matt

Tags (3)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

"If there are no records counted, list the host."
This is an inherently hard thing to do in a Splunk search - if a host doesn't have events returned by the search, you won't easily get it listed. There has to be a list of "expected hosts" to compare this to.

Here's one way to approach the issue:

  sourcetype="WinEventLog:Security" EventCode=4800 | stats count as locks by host
| append [tstats count where sourcetype="WinEventLog:Security" earliest=0 latest=now by host]
| stats first(locks) as locks by host
| where isnull(locks)

The first line grabs your lock events from your selected time range, the second line grabs all hosts present over all time, the third line collapses the lock count for each host, and the final line only keeps hosts that don't have lock events in the time range.

H/T to @dwaddle for tstats time ranges!

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

"If there are no records counted, list the host."
This is an inherently hard thing to do in a Splunk search - if a host doesn't have events returned by the search, you won't easily get it listed. There has to be a list of "expected hosts" to compare this to.

Here's one way to approach the issue:

  sourcetype="WinEventLog:Security" EventCode=4800 | stats count as locks by host
| append [tstats count where sourcetype="WinEventLog:Security" earliest=0 latest=now by host]
| stats first(locks) as locks by host
| where isnull(locks)

The first line grabs your lock events from your selected time range, the second line grabs all hosts present over all time, the third line collapses the lock count for each host, and the final line only keeps hosts that don't have lock events in the time range.

H/T to @dwaddle for tstats time ranges!

martin_mueller
SplunkTrust
SplunkTrust

stats is a search command: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Stats - similarly append, tstats, where.
count is one of many stats functions, doing just that - counting events, in this case grouped by a field.

The parsing job part takes a while because it silently includes running the subsearch (square brackets) that goes through ALL your data, crawling for hosts that might matter to your search. For efficiency gains you could maintain a lookup file with all your known hosts so you don't have to recompute it over and over again.

mbrownec
Explorer

Thank you!

I like the pipeline structure. I'm assuming that stats is like a class, and count is like a function that just counts whatever's piped in (the returned records)? etc etc with the other members of the pipeline?

Although the "parsing job" step seems to take much longer, it appears that this returns only the events I wish (93), versus a sub-search option that I kludged together from creeping through other answers (returning 1.5 million records):

sourcetype="WinEventLog:Security" 
 | stats count by host 
 | search NOT [
     search sourcetype="WinEventLog:Security" EventCode=4800
     | table host 
     | dedup host ]
 | stats count by host
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...