topic Re: Help with a join in Splunk Search

Help with a join

ccsfdave — Thu, 23 Jun 2016 17:56:03 GMT

I have a search that does work but takes forever because of the amount indexed by the ASA. I was thinking if it was reversed and looked at the fireeye first which is a smaller set, it could speed up the search. Please advise me on what I may be able to do to speed up the search results:

Works: index=ASA host="x.x.x.x" "%ASA-6-302013" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s" | search outside="y.y.y.y" |join outside [search index=fe Trojan.Ransomware.Locky cs5="*" |rename cs5 as outside]

Does Not Work: index=fe Trojan.Ransomware.Locky cs5="y.y.y.y" |rename cs5 as outside |join outside [index=ASA host="x.x.x.x" "%ASA-6-302013" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s"]

Thanks!

Re: Help with a join

somesoni2 — Thu, 23 Jun 2016 18:14:46 GMT

I don't see the purpose of join here. Your filter "| search outside="y.y.y.y" is restricting the events from index=ASA, I would rather do like this

index=ASA host="x.x.x.x" "%ASA-6-302013" "outside:y.y.y.y/" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s"

Re: Help with a join

diogofgm — Thu, 23 Jun 2016 18:16:13 GMT

The second search you are missing search term at the beginning of the subsearch

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 18:19:03 GMT

Oh that was an oversight of the cut and paste...it's there in real life 😉

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 18:21:13 GMT

The purpose of the join is that the FireEye and ASA see different data and I would like to choose from both when I create a form seeded table

Re: Help with a join

woodcock — Thu, 23 Jun 2016 18:35:12 GMT

Try this:

(index=ASA host="x.x.x.x" "%ASA-6-302013") OR (index=fe Trojan.Ransomware.Locky cs5="*")
| rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s"
| search index=fe OR outside="y.y.y.y"
| eval outside=if((index=fe), cs5, outside)
| stats dc(index) AS indexCount values(*) AS * BY outside

You now have a fully joined set:
For left Join, add this:

 | search index=ASA AND indexCount>1

For right join, add this:

 | search index=fe AND indexCount>1

For inner join, add this:

| search indexCount>1

For outer join, add this:

| search indexCount=1

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 19:00:41 GMT

This is a great format but I can't say a ton faster - which is what I was originally after with this request. Perhaps it makes no difference which dataset comes first when searching with a join.

Is there a way to transaction the above that may make a difference?

Re: Help with a join

woodcock — Thu, 23 Jun 2016 19:02:53 GMT

Try changing the first line to this:

 (index=ASA host="x.x.x.x" "%ASA-6-302013") OR (index=fe TERM(Trojan.Ransomware.Locky) cs5="*")

Re: Help with a join

somesoni2 — Thu, 23 Jun 2016 19:06:19 GMT

Give this a try as well

index=ASA host="x.x.x.x" "%ASA-6-302013" "*outside:y.y.y.y/*" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s" | eval From=1| append [search index=fe Trojan.Ransomware.Locky cs5="*" |rename cs5 as outside | eval From=2] | stats values(*) as * by outside | where mvcount(From)=2 | fields - From

AND

  index=ASA host="x.x.x.x" "%ASA-6-302013" "*outside:y.y.y.y/*" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s" | eval From=1| append [search index=fe Trojan.Ransomware.Locky cs5="*" |rename cs5 as outside | eval From=2] | search outside="y.y.y.y"| stats values(*) as * by outside | where mvcount(From)=2 | fields - From

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 19:16:31 GMT

These are blazing fast!

Re: Help with a join

somesoni2 — Thu, 23 Jun 2016 19:22:37 GMT

The trick is to filter as early as possible. (filtering ASA result in the base search itself)

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 19:24:29 GMT

In my mind I knew something like that but if I could have vocalized what I needed to do, I probably could have accomplished it! Thank you so much for your help!

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 20:09:38 GMT

@somesoni

rather than the stats in stats values(*) as * by outside

I would rather get just the values themselves rather than the digested stats. Essentially the search provided gives me one result with all joined info from something like 750 events. I would like the table to have 750 results. If I remove the stats, it give me the format but excludes the FireEye data. - actually sorting it anther way, removing the stats, where, and fields - commands gives me everything but the ASA and FireEye are on different rows.

Do you have any suggestions?

Re: Help with a join

somesoni2 — Thu, 23 Jun 2016 20:18:09 GMT

In that case you can use join instead of append-stats (will be slower). The append-stats would reduce the number of rows based on a common field (outside here). Generally a primary key is used to reduction in rows is somewhat acceptable/required in most requirements.

index=ASA host="x.x.x.x" "%ASA-6-302013" "*outside:y.y.y.y/*" | rex "outside:(?<outside>\b\d+.\d+.\d+.\d+\b)\/\d+\s.*inside:(?<inside>\b\d+.\d+.\d+.\d+\b)\/\d+\s" | join outside [search index=fe Trojan.Ransomware.Locky cs5="*" |rename cs5 as outside ]

Re: Help with a join

ccsfdave — Thu, 23 Jun 2016 20:27:39 GMT

That is exactly what I need and maybe not blazing fast but only blistering 😉