Splunk Search

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

mhamill
Engager

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?

Tags (1)
0 Karma
1 Solution

cramasta
Builder

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)

View solution in original post

cramasta
Builder

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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