Splunk Search

tstats joined to index query using stats

dmitrynt
Engager

Hello team,

I know I can use stats instead of join.  For our purposes we sometimes do that with 2 different indexes.

Now we have a one huge index from which we took some fields and we now have "data model" which i can query using tstats.  Problem is when I need to join result data from tstats with results from another index.  Is this possible?

I have following query (psedo query):

index=abc fieldX IN (Mary John Bob)

OR

| tstats values(a) values(b) where fieldY=xy by _time span=1s

| stats values(somevalue) as SomeA, dc(index) as idx, values(fieldX) as X by CommonName

 

Labels (3)
0 Karma

dmitrynt
Engager

with [search index... you are creating a subsearch which has limitations of 100k events i think.  I can't do subsearches as index is very big, think millions..

0 Karma

livehybrid
Super Champion

Hi @dmitrynt 

If you're concerned about hitting subsearch limits then run your index= search first, then append the tstats. 

Note - The default limits for append by default are 10,000 *results* and max 60 second execution time, but I would hope that your tstats runs faster than this and returns less < 10k results! This limit is based on *returned results* not number of events scanned, so applying stats (for example) in an append can also help with these limits.

🌟 Did this answer help you? If so, please consider:

  • Adding karma to show it was useful
  • Marking it as the solution if it resolved your issue
  • Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

0 Karma

dmitrynt
Engager

The only reason we created accelerated model is so that we can "return" 1 million events in few seconds.  Therefore, I'm not sure append fits this.

original non-working scenario due to huge index=a

index=a OR index=b
stats (where matched in 2 indexes) by FieldA

 

Need to make work

index=b OR | tstats  values.....

stats (where matched in 2 indexes??) by FieldA

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Even less. By default, unless we're talking join which has different limits, it's just 10k results.

Back to your original question... I'm not sure what you want to do to be honest. What do you want to join with what. And what results you are getting from each of those searches.

BTW you don't have to use tstats to search from datamodel (but you might want to if you want to aggregate quickly if your DM is accelerated; otherwise it might be slower than normal search)

0 Karma

dmitrynt
Engager

Here is my orignal query.  I have to mask a lot of code and evals. Sorry.  Probably ignore that i do eventstats and than stats as im doing a lookup which im not showing and getting columns there.

Question is: if index=A is now Accelerated model.  How can i join results of index query with tstats results without using sub-searches or anything that would limit it.

 

(index=a
OR
(index=b DistinguishedName IN ("ou=a" "ou=b")
| eventstats values(src_ip) as SourceIP dc(index) as idx values(OU) as OU by Account_Name
| search idx=2
| where index="a"
| stats dc(src_ip) as IP earliest(T) as FirstOccurance latest(T) as LatestOccurance values(OU) as Location count by Account_Name

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Well, that's not gonna be easy.

With this many results not only you can't use append but also eventstats is not a good idea.

Unfortunately, the less precise you are about your use case the more probability that you will get a "no can do" answer.

Maybe you should work on your extractions and/or initial filtering, maybe it's one of the rare cases where adding indexed field would help... we don't know. We are not aware what problem you're trying to solve.

0 Karma

livehybrid
Super Champion

Hi

Yes, you can combine tstats results with index search results using append , then aggregate with stats on a common field. I would avoid join for performance reasons.

index=abc fieldX IN (Mary John Bob)
| stats values(somevalue) as SomeA values(fieldX) as X by CommonName
| append [| tstats values(a) as a values(b) as b where datamodel=YourDataModel fieldY=xy by _time span=1s CommonName ]
| stats values(a) as a values(b) as b values(SomeA) as SomeA values(X) as X dc(index) as idx by CommonName

Run raw index search grouped by CommonName.

Append tstats to get data model results grouped by CommonName.

The final stats aggregates all fields by append0, effectively "joining" the datasets.

 

🌟Did this answer help you? If so, please consider:

  • Adding karma to show it was useful
  • Marking it as the solution if it resolved your issue
  • Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...