topic Re: How to combine multiple complex searches into 1 output table? in Splunk Search

How to combine multiple complex searches into 1 output table?

jonglim — Wed, 25 Jul 2018 14:25:21 GMT

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!

Re: How to combine multiple complex searches into 1 output table?

somesoni2 — Wed, 25 Jul 2018 15:52:09 GMT

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
]

Re: How to combine multiple complex searches into 1 output table?

jonglim — Wed, 25 Jul 2018 17:08:21 GMT

append works! thanks!

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

Re: How to combine multiple complex searches into 1 output table?

the_wolverine — Tue, 29 Sep 2020 20:38:56 GMT

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.