How to combine my two searches for Windows WMI Data (Host, OS, Version, SP and Number of Installed updates) into one report?

[JDukeSplunk]

Hello,

I have two different searches that return the data that I would like to see in one report. However, I am having some trouble combining them. Each of these reports pulls from WMI data captured multiple times per day, so it is necessary to do some kind of dedup to keep the results actual and not cumulative. I am hoping someone can help me string them together.

Here are the two that work on their own.
OS Data of the host.

The raw WMI event looks like this

20150727132545.862694
Caption=Microsoft Windows Server 2008 R2 Standard 
ServicePackMajorVersion=1
ServicePackMinorVersion=0
Version=6.1.7601
wmi_type=Version

Search

sourcetype="WMI:Version" | rex "Caption=(?<OS>.*)" |dedup 1 host| table host, OS, Version, ServicePackMajorVersion, ServicePackMinorVersion

This returns a single line per host.
What I want is to add one more column with a total count of all Windows KB Hotfixes installed on that host. The data from this next search.

The second one that works is this. It very simply counts the rc(HotFixID) by host.

Raw data event.

20150727132809.885587
Description=Update
FixComments=
HotFixID=KB3068708
InstalledBy=NT AUTHORITY\SYSTEM
InstalledOn=6/25/2015
ServicePackInEffect=
wmi_type=InstalledUpdates

Search

sourcetype="WMI:InstalledUpdates" |stats dc(HotFixID) as "Number" by host

In this instance, it returns Hostname and the number 332.

I have this as a stab at combining them, but all it returns is the table with only "Patch" populated with the correct number, 332.

(sourcetype="WMI:InstalledUpdates" OR sourcetype="WMI:Version")  | stats dc(HotFixID) as "Patch" |rex "Caption=(?<OS>.*)"| table host, OS, Version, ServicePackMajorVersion, ServicePackMinorVersion, Patch

Thanks in advance!
-JD

[somesoni2 · Accepted solution]

Try something like this

(sourcetype="WMI:InstalledUpdates" OR sourcetype="WMI:Version")  |rex "Caption=(?<OS>.*)" | table host, OS, Version, ServicePackMajorVersion, ServicePackMinorVersion, HotFixID | stats dc(HotFixID) as "Patch" values(*) as * by host

OR

(sourcetype="WMI:InstalledUpdates" OR sourcetype="WMI:Version")  | eventstats dc(HotFixID) as "Patch" by host | where sourcetype="WMI:Version" |rex "Caption=(?<OS>.*)"| table host, OS, Version, ServicePackMajorVersion, ServicePackMinorVersion, Patch

[JDukeSplunk]

Somesoni2,

You put me on the right track enough to cobble it out the rest of the way, thank you. What I ended up using was a variation on your bottom code. It worked, except it put two lines for every host.

(sourcetype="WMI:InstalledUpdates" OR sourcetype="WMI:Version")  | eventstats dc(HotFixID) as "Number of Patches" by host | where sourcetype="WMI:Version" |rex "Caption=(?<OS>.*)"| dedup 1 host|table host, OS, Version, ServicePackMajorVersion, ServicePackMinorVersion, "Number of Patches"