Part TWO: Need a little help troubleshooting my subsearch...

[packet_hunter]

This query works great

index=fireeye sourcetype=hx_json [search index=fireeye sourcetype=hx_cef_syslog   act="Detection IOC Hit" | table dhost | format | rex mode=sed field=search "s/dhost=//g"] | stats values(ioc_name) values(*username) values(alert.host.hostname) values(alert.host.os) values(alert.host.ip) values(alert.event_type) by _time

except I need another (additional) field value in the results from the first sourcetype

index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" | stats  values(ioc_name) as IOC 

I am not sure if this is possible, but I would like to pass the ioc_name field value pair to the results but I believe by formatting dhost precludes that...

Any ideas, or am I going about this the wrong way...

Thank you

[somesoni2 · Accepted solution]

So that's the problem. There are no matching alert_host.hostname in hx_cef_syslog, hence empty ioc_name. Are you sure you're mapping the correct field? (the filter subsearch is doing text-based search, not field based, so it might be the case the value of dhost is appearing elsewhere??)

[packet_hunter]

In hx_cef_syslog there are two field choices to find X

src_host
dhost

in hx_json there is just on field to find X

alert.host.hostname

I will try the other field name above, but like you say the field value pairs are different in each sourcetype. My original goal was to tie the ioc_name to alert.host.hostname... but I have this query (below) so I think I am good for now, thank you!!! if you convert your last response to an answer I will accept.

(index=fireeye sourcetype=hx_json alert.host.hostname=*) OR (index=fireeye sourcetype=hx_cef_syslog dhost=*)| eval match_host=coalesce(alert.host.hostname, dhost) | stats values(alert.event_at) values(ioc_name) values(*username)  values(alert.host.os) values(alert.host.ip) values(alert.event_type) values(match_host)

[somesoni2]

Your subsearch with sourcetype=hx_cef_syslog is adding a filter to main search, it's can't pass a value that can be displayed in the main search when subsearch is used as filter. What's your requirement with field ioc_name?

[packet_hunter]

Thanks for the reply, I was thinking as you stated, I follow what you posted.

I just need the IOC name included in the results, unfortunately sourcetype hx_json does not include it where I can grab it.

[packet_hunter]

I must have inadvertently deleted your latest post when I removed a duplicated comment I made.

I tried your code
index=fireeye sourcetype=hx_json [search index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" | table dhost | rename dhost as search | format ] OR (index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" ) | stats values(ioc_name) values(*username) values(alert.host.hostname) values(alert.host.os) values(alert.host.ip) values(alert.event_type) by _time

but it did not work, there is a time difference between the hx_json events and hx_cef_syslog events, a few seconds difference...

I am using the dhost=x in sourcetype=hx_cef-syslog and alert.host.hostname=x in sourcetype=hx_json, where x is a computer name... the computer name value is the key I am using.

I am also trying coalesce
(index=fireeye sourcetype=hx_json alert.host.hostname=) OR (index=fireeye sourcetype=hx_cef_syslog dhost=)| eval match_host=coalesce(alert.host.hostname, dhost) | stats values(ioc_name) by match_host

but I am still going thru the fields to see if I can grab everything I need...

[packet_hunter]

as long as I don't use: by _time
this query works
(index=fireeye sourcetype=hx_json alert.host.hostname=) OR (index=fireeye sourcetype=hx_cef_syslog dhost=)| eval match_host=coalesce(alert.host.hostname, dhost) | stats values(ioc_name) values(*username) values(alert.host.os) values(alert.host.ip) values(alert.event_type) values(match_host)

but I do need time, maybe I will just use the alert time value... unless you have a better way to write this...

(index=fireeye sourcetype=hx_json alert.host.hostname=) OR (index=fireeye sourcetype=hx_cef_syslog dhost=)| eval match_host=coalesce(alert.host.hostname, dhost) | stats values(ioc_name) values(*username) values(alert.host.os) values(alert.host.ip) values(alert.event_type) values(match_host) values(alert.event_at)

Thank you

[somesoni2]

Give this a try (the eventstats adds field ioc_name to sourcetype=hx_json events based on matching hostname and then where clause remove hx_cef_syslog events as they're not required.)

index=fireeye sourcetype=hx_json [search index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" | table dhost | rename dhost as search | format ] OR (index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" ) | eval match_host=coalesce('alert.host.hostname', dhost) | eventstats values(ioc_name) as ioc_name by match_host | where sourcetype="hx_json" | stats values(ioc_name) values(*username) values(alert.host.hostname) values(alert.host.os) values(alert.host.ip) values(alert.event_type) by _time

[packet_hunter]

works but the ioc_name is from the hx_cef_syslog events.... which was the original reason why I need to do all this...

I will keep poking around with your code

Thank you

[somesoni2]

So with above query you're not getting your ioc_name values?? We're using hx_cef_syslog events only to extract ioc_name values (eventstats).

[packet_hunter]

correct - I am not getting ioc_names.... and yes ioc_name is only in hx_cef_syslog...

[somesoni2]

Then the eval-eventstats to get it is not working. Do field 'alert.host.hostname' in hx_json and dhost in hx_cef_syslog has exact same value (case sensitive)?

[packet_hunter]

yes
index=fireeye sourcetype=hx_json |stats values(alert.host.hostname)

and

index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" |stats values(dhost)

give identical results

[somesoni2]

Run this and see if you get ioc_name_new column populated for all rows

index=fireeye sourcetype=hx_json [search index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" | table dhost | rename dhost as search | format ] OR (index=fireeye sourcetype=hx_cef_syslog act="Detection IOC Hit" ) | table alert.host.hostname dhost ioc_name | eval match_host=coalesce('alert.host.hostname', dhost) | eventstats values(ioc_name) as ioc_name_new by match_host 

[packet_hunter]

I get four column headings
alert.host.hostname with a value
dhost with no value
ioc_name with no value
match_host with a value (same as alert.host.hostname )