How to filter down events with specific string value from a multivalue field and then to compare and pick values as per the condition?

[sureshmurgan]

Windows update installation logs from machines are forwarded every day in Splunk.
In our windows environment, some windows update installations might fail and get logged as failed in the log, many get installed and gets logged as installed. These failed updates will try again to install another time and may get installed successfully or may end up as a failure again.

Requirement: Take all the values from the installation logs (ie., all the values in a field) filter out the failed installation which hasn't been able to get installed successfully yet.

[sureshmurgan · Accepted solution]

index=windowsevents sourcetype="System" SourceName=WindowsUpdate

           EventCode=19 OR EventCode=20

           |rex field=Message "(?P<Status>Installation\s\w+):.*(?P<Update>KB\d+).*"


           |stats latest(_time) as Time latest(Status) as Status latest(Message) as Message by host Update
           |search Status="Installation Failure"

[sureshmurgan]

Here's the query that's written till now. This will fetch all the Installation success and failure events and going to give the latest result. There could be multiple updates and some might have failed and other updates that came through would have got installed fine. So, this query is only going to give me the latest log where its failure. I need to get all the installation failure logs with the respective updates.
Not sure if this is this clear enough?

index=sccm_uk source="C:\Windows\CCM\Logs\WUAHandler.log" sourcetype=WindowsCCMLogs host="*" (ADSite_Membership="Installation job encountered some failures") OR
(ADSite_Membership="Installation of updates completed.")

           |stats latest(ADSite_Membership) as WUAInstallError by host
           |search WUAInstallError!="Installation of updates completed."                 
           |dedup host
           |stats dc(host) as #Hosts by WUAInstallError
           |sort 0 - #Hosts
           |addcoltotals #Hosts

[sureshmurgan]

I got the solution. I am using windows events to keep a track of the installation failure and got a query working for it. Thanks!

[VatsalJagani]

If you can, please share your solution as an answer here and accept it.

[sureshmurgan]

index=windowsevents sourcetype="System" SourceName=WindowsUpdate

              EventCode=19 OR EventCode=20

              |rex field=Message "(?P<Status>Installation\s\w+):.*(?P<Update>KB\d+).*"


              |stats latest(_time) as Time latest(Status) as Status latest(Message) as Message by host Update
              |search Status="Installation Failure"

[sureshmurgan]

This gives me the intended result. Thanks VatsalJagani for your help with this.

[VatsalJagani]

@sureshmurgan - Could you please share _raw event sample and query that till now you have wrote if any?

[sureshmurgan]

Hi Vatsal, thanks for responding.
Here's the query that's written till now. This will fetch all the Installation success and failure events and going to give the latest result. There could be multiple updates and some might have failed and other updates that came through would have got installed fine. So, this query is only going to give me the latest log where its failure. I need to get all the installation failure logs with the respective updates.
Not sure if this is this clear enough?

index=sccm_uk source="C:\Windows\CCM\Logs\WUAHandler.log" sourcetype=WindowsCCMLogs host="" (ADSite_Membership="Installation job encountered some failures") OR
(ADSite_Membership="Installation of updates completed.")

              |stats latest(ADSite_Membership) as WUAInstallError by host
              |search WUAInstallError!="Installation of updates completed."                 
              |dedup host
              |stats dc(host) as #Hosts by WUAInstallError
              |sort 0 - #Hosts
              |addcoltotals #Hosts

[VatsalJagani]

Could you please try:

index=sccm_uk source="C:\\Windows\\CCM\\Logs\\WUAHandler.log" sourcetype=WindowsCCMLogs host="" (ADSite_Membership="Installation job encountered some failures") OR 
(ADSite_Membership="Installation of updates completed.")
|stats list(ADSite_Membership) as WUAInstall by host

Could you please check value of WUAInstall? Is this what you want? It must be showing all logs(ADSite_Membership) of installation.