Splunk Search

How to combine multiple complex searches into 1 output table?

jonglim
New Member

I think I didn't describe my question properly because I don't really have a good grasp of Splunk Jargons but here are more details.

this is search # 1:

index ="12345" sourcetype = "system_database"
| fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp   
| join deviceName [ | inputlookup manual_db.csv ]
| join productFamily [ | inputlookup manual_software_db.csv ]
| join productFamily [ | inputlookup manual_vulnerability_list.csv ]
| table deviceName, productFamily, ipAddress, Advisory_ID, Tower, swVersion, reco_swVersion, swVersion_Fixed
| dedup ipAddress, deviceName
| sort productFamily
| where swVersion_Fixed > swVersion

This produces a table with 8 columns and 20 lines.

This is search #2:

    index ="12345" sourcetype = "system_database"
    | fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp 
    | join deviceName [ | inputlookup manual_db.csv ]
    | join productFamily [ | inputlookup manual_software_db.csv ]
    | search swType = "105"
    | join deviceId [ search index="6789" sourcetype=output_command_here| spath status | search status=Enabled ]
    | table deviceName, productFamily, ipAddress, Tower, swVersion, reco_swVersion, swType
    | dedup ipAddress, deviceName

This produces a table with 8 columns and 32 lines.

The column size and headers are identical on both searches. I am trying to combine the results into 1 output. I tried multisearch but that won't work due to the use of 'join'. Please help!

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

You can use append command to combine both results, like this (also, remove few joins on lookup tables, use lookup command instead)

index ="12345" sourcetype = "system_database"
     | fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp 
    | dedup ipAddress, deviceName
     | lookup  manual_db.csv  deviceName 
     | lookup  manual_software_db.csv productFamily 
     | search swType = "105"
     | join deviceId [ search index="6789" sourcetype=output_command_here| spath status | search status=Enabled ]
     | table deviceName, productFamily, ipAddress, Tower, swVersion, reco_swVersion, swType
| append [search index ="12345" sourcetype = "system_database"
 | fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp   
| dedup ipAddress, deviceName 
 | lookup manual_db.csv deviceName 
 | lookup manual_software_db.csv productFamily 
 | lookup manual_vulnerability_list.csv productFamily 
 | where swVersion_Fixed > swVersion
 | table deviceName, productFamily, ipAddress, Advisory_ID, Tower, swVersion, reco_swVersion, swVersion_Fixed
 | sort productFamily
]

View solution in original post

0 Karma

the_wolverine
Champion

instead of join, it may be more efficient to use lookup command. E.g.

index ="12345" sourcetype = "system_database" | lookup manual_db.csv deviceName | lookup manual_software_db.csv productFamily | lookup manual_software_db.csv | search swType=105 ...

Every subsearch is a search which needs to complete before Splunk can proceed to the next step so it is always a good idea to reduce the number of joins/subsearches wherever possible.

0 Karma

jonglim
New Member

append works! thanks!

the lookup had som problem sso i kinda reverted to join.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can use append command to combine both results, like this (also, remove few joins on lookup tables, use lookup command instead)

index ="12345" sourcetype = "system_database"
     | fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp 
    | dedup ipAddress, deviceName
     | lookup  manual_db.csv  deviceName 
     | lookup  manual_software_db.csv productFamily 
     | search swType = "105"
     | join deviceId [ search index="6789" sourcetype=output_command_here| spath status | search status=Enabled ]
     | table deviceName, productFamily, ipAddress, Tower, swVersion, reco_swVersion, swType
| append [search index ="12345" sourcetype = "system_database"
 | fields deviceId, deviceName, ipAddress, swType, productFamily, swVersion, timeStamp   
| dedup ipAddress, deviceName 
 | lookup manual_db.csv deviceName 
 | lookup manual_software_db.csv productFamily 
 | lookup manual_vulnerability_list.csv productFamily 
 | where swVersion_Fixed > swVersion
 | table deviceName, productFamily, ipAddress, Advisory_ID, Tower, swVersion, reco_swVersion, swVersion_Fixed
 | sort productFamily
]
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...