I believe that we have computers on our domain that are not actively being used by users and I would like to highlight that to see if we can recoup those computers elsewhere. How can I identify the last time any user logged onto the computer and then translate that to the number of days since the computer was logged onto by anyone? I would like the end result to be a table of ComputerName, lastlogontimestamp, and actual number of days since last logged on by anyone in descending order
This kinda falls into the category of searching for something that is not there. it's a challenge. See https://www.duanewaddle.com/proving-a-negative/ for a good discussion of it.
The first assumption is you have authentication logs in Splunk going back as far as needed to prove disuse.
Then there are two methods you could use. The first looks for the most recent logon for all computers.
index=foo sourcetype=someauthtype | stats max(_time) as lastLogon by ComputerName
| eval days=round((now() - lastLogon)/86400, 0)
| table ComputerName, lastLogon, days
The second approach is similar, but allows for computers with no record of a logon. It requires a list of all ComputerName values in a CSV file.
index=foo sourcetype=someauthtype | fillnull value=0 lastLogon _time
| inputlookup append=true computers.csv
| stats max(_time) as lastLogon by ComputerName
| table ComputerName, lastLogon, days
Thanks...I will try the first one tomorrow because I think that will work.
@tonygpe Curious. Did this work for you. Looking for a similar solution