Having issues with Subquery when comparing two searches of same source at different points in time

[mhamill]

We're trying to compare searches from our Security source, trying to see if someone hasn't logged in within the last 20 days (if they logged in 21 days ago, but not 20 days ago or sooner, it displays their name.) What I have so far:

sourcetype="WMI:WinEventLog:Security" CategoryString="Logon" Workstation_Name="" Type="Audit Success" earliest=-20d@d latest=now | stats values(Account_Name) |rename values(Account_Name) as LogonsNew | mvexpand LogonsNew | eval New="y" | table Logons LogonsNew | join type=outer [search sourcetype="WMI:WinEventLog:Security" CategoryString="Logon" Workstation_Name="" Type="Audit Success" earliest=-21d@d latest=-20d@d | stats values(Account_Name) |rename values(Account_Name) as LogonsOld | mvexpand LogonsOld| eval Old="y" | table Logons LogonsOld] | where New != "y" AND Old = "y"

The initial search makes an expanded Multivalue list of unique logons that have happened within the timeframe. This part works normally, so it shouldn't be the problem. However, When put the subquery with it, no data comes up for the 'LogonsOld' table. Am I doing something wrong here?

What I think might be the problem is that the searches look for the same field, and then the name change affects both of the fields. Is there any way to make it so that wouldn't happen?

[cramasta · Accepted solution]

Try this different approach that will save you the cost of using a join

sourcetype="WMI:WinEventLog:Security" CategoryString="Logon" Workstation_Name="" Type="Audit Success" earliest=-25d@d | eval age=now()-_time | eval recentlogon=if(age <=1728000,"yes","no") | stats values(recentlogon) AS recentlogon by Account_Name | search recentlogon!=yes

Basically what this does...
if the time of the logon event is less than 20 days old (from the time of the search running using the now() function), then recentlogon=yes , otherwise if the logon event is older than 20 days recentlogon=no. next we do a stats by Account_Name and only display events where recentlogon didn't equal yes (that they didnt recently logon)

[mhamill]

Thank You! Your solution was far more straightforward than mine.