I have the following 2 alerts and need to correlate them. The first one is looks for an OS reboot. The second one looks for the Event Logging Service Shutdown.
How can I correlate them to alert when Event Logging Service shuts down but NOT related to an OS reboot?
index=wineventlog EventType=4 EventCode=12 OR EventCode=13
| table _time host Message
| index="wineventlog" EventCode=1100 ComputerName!=HD* ComputerName!=HL*
| table _time ComputerName name source
I run Splunk Cloud basic.
Something like the following should work, where you pull in both sets of information, create a joining field, and aggregate with a stats field. Make sure to run this over a finite enough time frame that you won't be pulling in multiple sources of information, else you'll have to add a bin statement to bucket time in a way that makes sense for what you're trying to do:
index=wineventlog (EventType=4 EventCode=12 OR EventCode=13) OR (EventCode=1100 ComputerName!=HD* ComputerName!=HL*) | eval host=coalesce(host, ComputerName) # assumption made here that ComputerName and host represent the same values | eval EventTypeFromOS=if(EventType IN (4, 12, 13), 1, 0) | eval EventTypeFromLoggingService=if(EventType=1100, 1, 0) | stats values(EventTypeFromOS) as EventTypeFromOS, values(EventTypeFromLoggingService) as EventTypeFromLoggingService by host |search EventTypeFromLoggingService=1 AND EventTypeFromOS=0
At a high level, this pulls in both events, creates a host field which is the coalesced field of host and ComputerName which I assume represent the same value, if not, change it to one that does, create a verbose field that represents if the event is from the first set of logs or the second, take the values of that by host (the field we created), and search for where loggingservice is not null and OS log is null (meaning it's an issue on one end and not the other).
Hope this helps
I modified your search a bit and with the search below, I get results up until I add in the |where clause. Also, I was able to remove the coalesce as the host field in both searches is the same (no need for ComputerName).
index=wineventlog (EventType=4 EventCode=13 host!=DL) OR (EventType=4 EventCode=1100 host!=DL)
| eval EventCodeFromOS=if(EventCode=13, 1, 0)
| eval EventCodeFromLoggingService=if(EventCode=1100, 1, 0)
| stats values(EventCodeFromOS) as EventCodeFromOS, values(EventCodeFromLoggingService) as EventCodeFromLoggingService by host
| where isnotnull(EventCodeFromLoggingService) AND isnull(EventCodeFromOS)
Without the where clause 30 day results of stats look like this:
host EventCodeFromOS EventCodeFromLoggingService
ABC 0 0
DEF 0 0
GHI 0 0
Any thoughts on where clause issue?
Ultimately, if there is a Logging service event but no OS shutdown event, I want to generate an alert.
Oops, I updated it. We want to search for the value to be 1 for the one event type and 0 for the other. In the future, going line by line down to debug is a good way to determine what the issue would be. In this case, after removing the where clause I would see there was a 1 and a 0, not a value and a null field, so I know i need to change the filtering criteria. You can write this without the two eval statements, just passing through a field from each log that is distinct, and searching for the absence of one of them, which is why I wrote it with a null check instead of a value check. Both ways work, this one is a little more verbose but clear what you're trying to accomplish. Please accept/upvote the answer if this works!
Also, you're returning a multivalue field because there are multiple errors in the time frame you're searching. You'll need to narrow it down to a more specific time frame , probably by adding a
| bin _time span=d as day before the stats command and adding day after host, i.e.
by host, day. This will bucket events by day, but you can bin the _time to be smaller, or use another field to better correlate the issues with each other, otherwise an OS issue from last week could correlate to a loggingservice issue from 2 weeks ago.