Splunk Search

Identifying stale computers on network

Durwood
Engager

I am looking to identify specific assets that have not been logged into in over a set time. I am fairly new to all of this and trying to learn in a more hands on way. I was wondering what would be the best way to accomplish this?

I was thinking something like this but I don't think this is right:

EventCode=4624 AND [|inputlookup append=t Computers.csv] NOT [inputlookup append=t Dont_search.csv] | dedup host | table _time,host,user | sort host

Computers.csv - Specific computers that I want to track.

Dont_search.csv - Accounts that I DO NOT want to track. 

I am hoping to show all computers on my list regardless of whether they were logged in too. Any help would be greatly appreciated!!!

Labels (3)
0 Karma

johnhuang
Motivator

Assuming that Computers.csv contains a field called "host" and Dont_search.csv contains "user".

 

 

source=WinEventLog:Security 4624 EventCode=4624 [| inputlookup Computers.csv | table host] earliest=-1d@d
| fields host user
| lookup Dont_search.csv user OUTPUT user AS filtered_user
| search NOT filtered_user=*
| stats max(_time) AS last_logon_time first(user) AS user BY host
| eval days_since_logon=ROUND((now()-last_logon_time)/86400, 2)
| eval last_logon_date=strftime(last_logon_time, "%Y-%m-%d")
| table host user days_since_logon last_logon_date
| append [| inputlookup Computers.csv | table host]
| dedup host | fillnull value="-"
| table host user days_since_logon last_logon_date

 

 

Includes domain field which should be more useful. 

 

source=WinEventLog:Security 4624 EventCode=4624 [| inputlookup Computers.csv | table host] earliest=-1d@d
| fields host user Account_Domain Security_ID
| lookup Dont_search.csv user OUTPUT user AS filtered_user | search NOT filtered_user=*
| eval domain=mvindex(Account_Domain, 1) | eval logon_id=mvindex(Security_ID, 1)
| search NOT domain="* *" | where host!=domain
| table _time host logon_id domain user
| stats max(_time) AS last_logon_time first(logon_id) AS logon_id first(domain) AS domain first(user) AS user BY host
| eval days_since_logon=ROUND((now()-last_logon_time)/86400, 2)
| eval last_logon_date=strftime(last_logon_time, "%Y-%m-%d")
| table host domain user logon_id days_since_logon last_logon_date
| append [| inputlookup Computers.csv | table host]
| dedup host
| table host domain user logon_id days_since_logon last_logon_date

 

 

 

0 Karma

Durwood
Engager

Thank you for the response! I am still having issues with the search excluding the users in the user column of my "Dont_Search.csv". Any ideas? I am very new to the Splunk game so apologies if I am asking something that is a bit elementary. 

0 Karma

johnhuang
Motivator

Could you provide a sample of the search output and also Dont_Search.csv?

0 Karma

johnhuang
Motivator

BTW, if your goal is to show real user logons to an interactive session, you should further filter the logon_type. For example:

 

source=WinEventLog:Security 4624 (EventCode=4624 AND (Logon_Type=2 OR Logon_Type=7 OR Logon_Type=10 OR Logon_Type=11))
[| inputlookup Computers.csv | table host] earliest=-1d@d

 

PickleRick
Ultra Champion

There are two issues with your search.

1. Your subsearches must return properly named columns. Are you sure that you don't need to do some "| rename"?

2. With subsearches provided this way you only add further conditions to your search. You will still not get any results if there are no events matching the condition set.

If you want to find which hosts didn't send anything, you'd have to append "fake" results from a pre-defined set of hosts, and then - for example - sum them with your found events. Then you'd see which results have zero ocurrences.

A rough idea:

<your search> | stats count by Computername
| append [ | inputlookup myhosts.csv | eval count=0 ]
| stats sum(count) by Computername

richgalloway
SplunkTrust
SplunkTrust

Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...