topic Re: Table not generated with join subsearch in Splunk Search

Table not generated with join subsearch

vantoryc — Wed, 04 Aug 2021 21:12:15 GMT

Hi,

We are sending a reduced size logs to out splunk to do some smarts. We realized for the past year or so one of our alerts is not working at all. Between that year we have upgraded splunk from 6.5.2 to latest 8.2.1 and also migrated it from the entire VM it sits on.

index=clean_security_events earliest=-1h | stats count as Events by SG mx_ip | join SG [search index=clean_security_events earliest=-720h latest=-1h | bin span=1h _time | stats count by SG _time | streamstats mean(count) as Average, stdev(count) as Deviation, max(count) as Peak by SG | dedup SG sortby -_time | eval Average = round(Average) | eval Variance = Deviation / Average] | where Events > (Average + (Deviation * (Variance + 10))) AND Events > (Average * 20) AND Events > 20000 AND Events > Peak AND Average > 50 | lookup mx2cx mx_ip | table ServerGroup mx_ip cx Events Average

The general idea is we send reduced security events from our app and use the above to determine if a given SG (hence the stat count as Events) is generating sudden high events compared to the last 30 days.

Upon trial and error if I narrow down to one mx_ip out of the 100s it works. I suspect that the subsearch is either generating too many events or the result are taking too long for the parent search and as a result we are getting empty tables.

Any idea how to fix this? My understanding is I can increase the limits but it is not recommended.

I was thinking to use some ML toolkit to detect outlier and that way I can replace two alerts (one for sudden uptick and one for sudden downtick)

Re: Table not generated with join subsearch

richgalloway — Thu, 05 Aug 2021 00:14:34 GMT

If SG is an indexed field then you can use | tstats to speed up the subsearch. Also, consider an accelerated datamodel.

Re: Table not generated with join subsearch

vantoryc — Tue, 10 Aug 2021 18:27:26 GMT

any pointers to get the data model setup with my use case?

I'm playing around right now but not much progress

Re: Table not generated with join subsearch

richgalloway — Tue, 10 Aug 2021 19:08:45 GMT

The datamodel needs to search the clean_security_events index and produce the _time, SG, and mx_ip fields.

What do you have so far?

Re: Table not generated with join subsearch

vantoryc — Tue, 10 Aug 2021 20:32:18 GMT

Hi,

Create data model just for the index
Extracted the SG and mx_ip filed
Went to pivot to get a table that shows the count by the SG per hour

Re: Table not generated with join subsearch

richgalloway — Tue, 10 Aug 2021 21:51:58 GMT

Back up a step. DMs don't create tables. Once you have a root search defined and fields extracted then save the DM and call it a day.

Re: Table not generated with join subsearch

vantoryc — Thu, 12 Aug 2021 18:13:14 GMT

Okay so I already have the DM, how does one go about utilizing it?

Sorry I'm confused here.

From what I found on my research, you create a DM and then you have to use it too , Im trying to find some practical examples on YT and other location but most of them are turning up empty.

Re: Table not generated with join subsearch

richgalloway — Thu, 12 Aug 2021 19:59:10 GMT

There are a few ways to use it. The tstats, from, and datamodel commands all support datamodels.

| tstats count as Events from datamodel=foo where earliest=-1h by SG mx_ip | join SG [| tstats count where earliest=-720h latest=-1h by SG _time span=1h | streamstats mean(count) as Average, stdev(count) as Deviation, max(count) as Peak by SG | dedup SG sortby -_time | eval Average = round(Average) | eval Variance = Deviation / Average] | where Events > (Average + (Deviation * (Variance + 10))) AND Events > (Average * 20) AND Events > 20000 AND Events > Peak AND Average > 50 | lookup mx2cx mx_ip | table ServerGroup mx_ip cx Events Average

If you accelerate the datamodel then the tstats command should be very fast.

Re: Table not generated with join subsearch

vantoryc — Wed, 01 Sep 2021 17:01:10 GMT

Hi Sorry got sidelined on other stuff.

So when I run the below command I do not get results, even if I remove either mx_ip or SG :

| tstats count as Events from datamodel=foo where earliest=-1h by SG mx_ip

But just running a simple tstat gives me instant output like this:

| tstats count as Events from datamodel=foo where earliest=-1h

I'm guessing my data model is wrong.

What kind of dataset I need to add? root event or root search?
1. I tried both and only simple tstats is outputted
Both times I then auto extracted the SG and mx_ip field. but set it to optional.

Re: Table not generated with join subsearch

richgalloway — Thu, 02 Sep 2021 11:58:30 GMT

Using the by option of tstats requires the datamodel to contain all of the fields specified. They can be optional, but the field is not present then it won't be counted by tstats.