Splunk Search

How to combine two tstat search to create one table?

lbrhyne
Path Finder

Hello,

I have created a datamodel which I have accelerated, containing two sourcetype. The goal is to add a field from one sourcetype into the primary results. The challenge I have been having is returning all the data from the Vulnerability sourcetype, which contains over 400K events. I have attempted several different searchs, none of which are the results i'm expecting. Basically I want to join the two tstats below without using join do to its limitations. The vulnerabilies should include the hostname and operating system. 

Any help would be apprectiated

 

 

|tstats summariesonly=f
     ``` latest(tenable.plugin_name) as "Vulnerability", ```
      values(tenable.first_found) as "First Found", 
      values(tenable.last_found) as "Last Found",
      values(tenable.risk_factor) as "Risk Factor", 
      values(tenable.in_the_news) as "In The News?", 
      values(tenable.vpr_score) as "VPR", 
      values(tenable.solution) as "Solution",
      values(tenable.see_also) as "See Also", 
      values(tenable.state) as "State"
      values(tenable.exploitability_ease) as "Exploitable Ease",
      values(tenable.exploit_available) as "Exploit Available"
      values(tenable.ip) as IP
     ``` latest(tenable.asset_hostname) as hostname```
      
     
             FROM datamodel="VulnMgt.tenable",
             WHERE sourcetype="tenable:io:vuln"
             by tenable.asset_uuid tenable.asset_hostname tenable.plugin_name 


 
|tstats summariesonly=t prestats=t append=t

            values(devices.operating_systems) as OS
            FROM datamodel="VulnMgt.tenable",
            WHERE sourcetype="tenable:io:assets"
            by tenable.asset_uuid tenable.hostnames

            | stats latest(*) as * count as total by tenable.asset_uuid

 

 


 

Labels (3)
0 Karma

hettervik
Builder

I think the way to go for combining tstats searches without limits is using "prestats=t" and "append=true". Note that you maybe have to rewrite the searches quite a bit to get the desired results, but it should be possible. Also note that you maybe have to create a table before using the normal stats command to merge the tstats searches (don't know why, but worked for me, perhaps something with having all the data on the search head instead of distributed on the indexers).

See thread here as well: https://community.splunk.com/t5/Splunk-Search/How-do-I-join-two-data-models-in-a-TSTATS-without-usin...

richgalloway
SplunkTrust
SplunkTrust

Instead of using the append option of tstats, try append with tstats in the subsearch.

| tstats summariesonly=t <<search1>>
| append [ tstats summariesonly=t <<search2>> ]
| stats latest(*) as * count as total by tenable.asset_uuid

 

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

lbrhyne
Path Finder

Thank you @richgalloway  That was one of the search I attempted and Splunk shows it as having a 50k limit.

Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...