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!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

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

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...