Splunk Search

Table not generated with join subsearch

vantoryc
Explorer

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)

Labels (3)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

vantoryc
Explorer

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

 

I'm playing around right now but not much progress

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

vantoryc
Explorer

Hi,

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

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

vantoryc
Explorer

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

vantoryc
Explorer

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.

 

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

 

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!