How to mix fields from two sourcetypes

[pacmac]

Hello,

I have these two searches:

sourcetype=pan:threat src IN (10.0.0.0/8, 192.168.0.0/16, 172.16.0.0/12)
| where raw_category = "web-advertisements"
| stats count by src
| sort count DESC

and

sourcetype="WinEventLog:Security" 
| stats count by Account_Name 
| top limit=1 Account_Name 
| table Account_Name 

The src field from "pan:threat" sourcetype will be found as "Source Address" or "Source Network Address" on "WinEventLog:Security" sourcetype.

I want to retrieve the Account_Name field from "WinEventLog:Security" for each src on "pan:threat" and show them in a table with src and count from "pan:threat" and Account_Name from "WinEventLog:Security". is this possible?

Thanks in advance.

[richgalloway · Accepted solution]

That can be done without using join.

(sourcetype=pan:threat src IN (10.0.0.0/8, 192.168.0.0/16, 172.16.0.0/12) raw_category = "web-advertisements") OR
sourcetype="WinEventLog:Security" 
| eval src=coalesce(src, 'Source Address', 'Source Network Address')
| stats values(*) as * by src
| stats count values(src) as src by Account_Name
| top limit=1 Account_Name 
| table src Account_Name count

[raduurjan]

Hello,

This can be possible using join command but you need to have 2 identical field names.
You can use eval or rename to make 2 identical fields and than use a left join on the common field.

You can find join documentation here:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

[pacmac]

Thanks for editing @evzhang