topic Re: Combine two source types using data models and join in Splunk Dev

Combine two source types using data models and join

Ojay87 — Tue, 29 Sep 2020 16:17:12 GMT

Hi everyone!

In Enterprise Security I am trying to combine results from two different source types by using "join" but facing problem with subsearch limits. My goal is to make a statistic table where the traffic data is coming from another log, but this traffic log is huge even if I narrow the search for one hour. All I really need is just fields "bytes_in" and "bytes_out" from traffic log, but now it is collecting everything. I have tried narrow the subsearch results, but not yet successfully.

Below search is the very basic setup which would work without any limits, but I cannot change them. Looking for any ideas how to bypass it.

| datamodel datamodel1 search | search attack="vulnerability1"
| join src_ip type=left overwrite=false [search index=logs sourcetype=traffic_log ] 
| stats sum(eval(round(bytes_in(1024*1024),2))) AS "Incoming Mb" sum(eval(round(bytes_out/(1024*1024),2))) AS "Outgoing Mb" by src_ip

Re: Combine two source types using data models and join

cmerriman — Thu, 19 Oct 2017 13:00:38 GMT

how much data is in the datamodel? if you flipped them around and had the datamodel as a subsearch, would you still run into limitations? Is there a way you can do some aggregations inside the subsearch as well as to the base search to limit the amount of events?

Re: Combine two source types using data models and join

Ojay87 — Thu, 19 Oct 2017 13:17:00 GMT

Thanks for your reply!

I get this error when switching rows of datamodel and subsearch or trying datamodel as a subsearch: "Error in 'SearchParser': The datamodel command can only be used as the first command on a search'.

Re: Combine two source types using data models and join

cmerriman — Tue, 29 Sep 2020 16:20:50 GMT

that shouldn't be a problem. just make sure it's formatted properly.

index=logs sourcetype=traffic_log|join src_ip type=left overwrite=false [| datamodel datamodelname datasetname search | search attack="vulnerability1"]| stats sum(eval(round(bytes_in(1024*1024),2))) AS "Incoming Mb" sum(eval(round(bytes_out/(1024*1024),2))) AS "Outgoing Mb" by src_ip

the problem might come with the datamodel timing out, perhaps, but it can be used as a subsearch, it is the first command in the search (the subsearch is it's own search).

have you tried using the pivot command instead? is the datamodel accelerated? again, is there a way to aggregate either before joining them together on the raw events? perhaps by summing the bytes in and out by src_ip in the traffic_log and using the datamodel/pivot as a subsearch with a distinct list of src_ip that had vulnerable attacks? (i'm completely guessing that that is what your datamodel is there for)

also, if you did the aggregate of bytes in/out by src_ip in the subsearch, you might not run into the limitation.

Re: Combine two source types using data models and join

DalJeanis — Tue, 29 Sep 2020 16:17:35 GMT

Assumption 1: You need bytes_in and bytes_out from traffic_log. But you only need that information if the src_ip is in the datamodel and marked as "vulnerability1".

Assumption 2: The frequency of src_ip in the datamodel and marked as "vulnerability1" in traffic_log are low/sparse relative to the total number of src_p in traffic_log.

index=logs sourcetype=traffic_log 
  [| datamodel datamodel1 search | search attack="vulnerability1" | dedup src_ip | table src_ip]
| stats sum(eval(round(bytes_in/(1024*1024),2))) AS "Incoming Mb" 
        sum(eval(round(bytes_out/(1024*1024),2))) AS "Outgoing Mb" by src_ip

Re: Combine two source types using data models and join

Ojay87 — Fri, 20 Oct 2017 07:43:55 GMT

Thanks, you were right. I wrote the commands incorrectly. Now, it works how I wanted! Many thanks for your support here!

Re: Combine two source types using data models and join

Ojay87 — Fri, 20 Oct 2017 07:47:47 GMT

Thank you for your reply! This is what I wanted as you described in your first assumption.

This was very helpful. Now it works!

Re: Combine two source types using data models and join

DalJeanis — Fri, 20 Oct 2017 17:14:21 GMT

@ojay87 - We're glad to help. Please accept the answer, so the question will show as closed.

Or instead, if @cmerriman's answer was the one that did the trick, rather than mine, then please let us know and we can convert her comment to an answer so you can accept that instead. We're all family around here.