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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...