tstats joined to index query using stats

[dmitrynt]

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

[dmitrynt]

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..

[livehybrid]

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

[dmitrynt]

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

[PickleRick]

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)

[dmitrynt]

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

[PickleRick]

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.

[livehybrid]

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