Are you a member of the Splunk Community?
- Find Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Having issues with Subquery when comparing two sea...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank You! Your solution was far more straightforward than mine.
Observe and Secure All Apps with Splunk
Splunk Decoded: Business Transactions vs Business IQ
Fastest way to demo Observability
