Just to put some closure to this thread, I had to resort to simply ignoring emails for rule #18152, the rule triggered by excessive win_authentication_failed group hits. I simply overwrote that rule and added the <options>no_email_alert</options> option to it. Although not what I was hoping to achieve it has stemmed the large amount of alert emails until the underlying issue on the database is resolved. It does add more overhead in that I have to check the console periodically since no emails for this are being sent, but good enough as a temporary workaround.
I didn't want to change the aggregate to a SID, as there are other win_authentication_failed hits from other sids that I didn't want to end up missing. If anyone does have an answer to making this one-off exception successfully work, I'm all ears - but for now it's good enough. Thanks for your input Maarten. - Bruce On Wednesday, November 22, 2017 at 2:54:35 PM UTC-5, Maarten Broekman wrote: > > That's bizarre. You can see in the group list in the alert that the > win_authentication_failed group isn't listed so I'm not sure why it would > end up in the aggregate. Very odd. I know it's going even further afield, > but changing the aggregate from if_matched_group to > <if_matched_sid>100150</if_matched_sid> might be needed. It might be > possible to just add if_matched_sid as another condition, but I'm not sure. > > --Maarten > > On Wednesday, November 22, 2017 at 2:02:28 PM UTC-5, Bruce Westbrook wrote: >> >> My apologies, you're right -- I pasted the wrong info, sorry about that. >> I was also editing things like the description at the same time, therefore >> the lack of consistency you identified. >> >> First answer -- alerts are sent at level 7 or above. The parent rule >> #18180 is not the one sending the alert as it's already a lower alert level >> (level=5). It's the composite child rule #18152 that does that, which is >> set to level=10. That rule is looking for >> <if_matched_group>win_authentication_failed</if_matched_group>, which >> based on your suggestion I removed by overwriting rule #18180. Yet it's >> still alerting. >> >> Second answer -- here's a recent alert directly from the alerts.log file, >> showing rule #18152 still triggering. >> >> >> ** Alert 1511375499.1421236953: - local,syslog,authentication_failures, >> pci_dss_10.2.4,pci_dss_10.2.5,pci_dss_11.4, >> 2017 Nov 22 13:31:39 (SERVER) any->WinEvtLog >> Rule: 18152 (level 10) -> 'Multiple Windows Logon Failures.' >> User: (no user) >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> 2017 Nov 22 13:31:36 WinEvtLog: Application: AUDIT_FAILURE(18456): >> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >> 'USERNAME'. Reason: Failed to open the explicitly specified database >> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >> >> >> If the first line of the alert is an indication of the groups it >> triggered on, I'm even more confused as it doesn't list the group that the >> rule is configured to trigger from, win_authentication_failed. I'm >> obviously not understanding something here. >> >> Here are the rules I created, based on your suggestion to overwrite >> #18180 to match only to the problematic log entry and NOT give it the group >> triggered by rule #18152, then add a custom rule that is a replacement for >> the original #18180 rule: >> >> <rule id="18180" level="5" overwrite="yes"> >> <if_sid>18105</if_sid> >> <id>^18456$</id> >> <match>Login failed for user 'USER'</match> >> <group>pci_dss_10.2.4,pci_dss_10.2.5,</group> >> <description>TEMP NOISE REDUCTION: MS SQL Server Logon Failure for >> 'USER'</description> >> </rule> >> >> >> <rule id="100150" level="5"> >> <if_sid>18105</if_sid> >> <id>^18456$</id> >> <group>win_authentication_failed,pci_dss_10.2.4,pci_dss_10.2.5, >> </group> >> <description>MS SQL Server Logon Failure - custom rule for #18180 >> </description> >> </rule> >> >> >> >> From what I can see, it looks like the problematic log entries are indeed >> hitting the revised rule #18180, which means they should not be getting >> tagged with the group win_authentication_failed -- yet rule #18152 still >> fires off based on these events still somehow being tagged with that >> group. I'm obviously not understanding something about how this is working >> since it's still triggering emails. >> >> I appreciate any further insight you or anyone can provide, thanks! >> >> *As a heads up, if you're not in the USA we have a 4-day weekend with >> Thanksgiving starting, so I don't plan to be looking at this again until >> Monday. :-)* >> >> >> >> On Wednesday, November 22, 2017 at 6:45:32 AM UTC-5, Maarten Broekman >> wrote: >>> >>> Two questions... >>> At what level do you get emails from the alerts? I noticed you didn't >>> change the level of the 18180 from a '5'. >>> >>> Do you have a sanitized version of the 'new' alert from the alert log >>> (as opposed to the ossec-logtest output)? That will show the groups on the >>> alert. >>> >>> I ask because the ossec-logtest output seems to show a different >>> description of 18180 than what should be there from the configured (and >>> displayed) rules you pasted. >>> >>> Maarten >>> >>> On Tuesday, November 21, 2017 at 10:42:23 AM UTC-5, Bruce Westbrook >>> wrote: >>>> >>>> Unfortunately that didn't work Maarten. After following that logic I >>>> am still getting all the email alerts for that account again. And yes, I >>>> restarted the OSSEC daemons after adding the rules :-) >>>> >>>> However, when I run the log entry against ossec-logtest, it appears to >>>> do what I want by matching my overwritten #18180 rule -- yet in reality it >>>> still sends an email due to it matching the #18152 composite rule (I'm not >>>> sure how to use ossec-logtest to test a composite rule with multiple >>>> log entries). >>>> >>>> Here are the rules I added: >>>> >>>> >>>> <!-- Rewrite rule #18180 to narrow down to bad SQL account and not >>>> add the 'win_authentication_failed' group --> >>>> <rule id="18180" level="5" overwrite="yes"> >>>> <if_sid>18105</if_sid> >>>> <id>^18456$</id> >>>> <match>Login failed for user 'USERNAME'</match> >>>> <group>pci_dss_10.2.4,pci_dss_10.2.5,</group> >>>> <description>MS SQL Server Logon Failure for 'dpa' only >>>> </description> >>>> </rule> >>>> >>>> <!-- Add new rule to take the place of rule #18180 after matching our >>>> bad SQL account --> >>>> <rule id="100150" level="5"> >>>> <if_sid>18105</if_sid> >>>> <id>^18456$</id> >>>> <group>win_authentication_failed,pci_dss_10.2.4,pci_dss_10.2.5, >>>> </group> >>>> <description>MS SQL Server Logon Failure for 'dpa' only >>>> </description> >>>> </rule> >>>> >>>> >>>> >>>> Here's the output from ossec-logtest: >>>> >>>> >>>> 2017/11/21 10:31:13 ossec-testrule: INFO: Reading local decoder file. >>>> 2017/11/21 10:31:13 ossec-testrule: INFO: Started (pid: 27437). >>>> ossec-testrule: Type one log per line. >>>> >>>> >>>> 2017 Nov 16 12:43:56 WinEvtLog: Application: AUDIT_FAILURE(18456): >>>> MSSQLSERVER: (no user): no domain: SERVER: Login failed for user >>>> 'USERNAME'. Reason: Failed to open the explicitly specified database >>>> 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn] >>>> >>>> **Phase 1: Completed pre-decoding. >>>> full event: '2017 Nov 16 12:43:56 WinEvtLog: Application: >>>> AUDIT_FAILURE(18456): MSSQLSERVER: (no user): no domain: SERVER: Login >>>> failed for user 'USERNAME'. Reason: Failed to open the explicitly >>>> specified database 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn]' >>>> hostname: 'SERVER' >>>> program_name: '(null)' >>>> log: '2017 Nov 16 12:43:56 WinEvtLog: Application: >>>> AUDIT_FAILURE(18456): MSSQLSERVER: (no user): no domain: SERVER: Login >>>> failed for user 'USERNAME'. Reason: Failed to open the explicitly >>>> specified database 'DATABASE'. [CLIENT: nnn.nnn.nnn.nnn]' >>>> >>>> >>>> **Phase 2: Completed decoding. >>>> decoder: 'windows' >>>> status: 'AUDIT_FAILURE' >>>> id: '18456' >>>> extra_data: 'MSSQLSERVER' >>>> dstuser: '(no user)' >>>> system_name: 'SERVER' >>>> >>>> >>>> **Phase 3: Completed filtering (rules). >>>> Rule id: '18180' >>>> Level: '5' >>>> Description: 'TEMP NOISE REDUCTION: MS SQL Server Logon Failure >>>> for 'USERNAME'' >>>> **Alert to be generated. >>>> >>>> >>>> >>>> What am I still missing? Any ideas? >>>> >>>> -- --- You received this message because you are subscribed to the Google Groups "ossec-list" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
