Splunk Search

Insert results of subsearch as single column in table

mxanareckless
Path Finder

Here is what I need to generate:

dbbakreport1.PNG

 

 

 

So far I have this search to generate everything but the right-most column:

index=Production sourcetype=df host="host*" MountedOn="/b1"
| stats latest(Size) as "Allocation", latest(Used) as "Usage"
| eval Environment="Production",
    "Server info (LDOM)"="host1,host2",
    "Mount Point"="/export/backups",
    "Backup Filesystem"="/backup01",
    "Local mount"="/b1"
| table Environment, "Server info (LDOM)", "Mount Point", "Backup Filesystem", "Local mount", "Allocation", "Usage"

Results:

dbbakreport2.PNG

 

 

 

 

And this search generates the contents for the right-most column in the spreadsheet:

index=DB sourcetype=db_size
| table source, TotalSizeGB
| eval TotalSizeGB=round(TotalSizeGB,2)
| sort -TotalSizeGB

Results:

dbbakreport3.PNG

 

 

 

 

How can I get the results of search #2 to appear in the final right-most column of search #1's results?

Thank you!

Labels (4)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

It looks like the empty row is from the append command.  It's blank because it only supplies one field which is not part of the table command.

It appears you omitted a key part of my second suggestion - the stats command.  That's the part that combines the results of the two searches.

---
If this reply helps you, an upvote would be appreciated.

mxanareckless
Path Finder

@richgalloway 

Thanks again for your reply. I've struggled to add the | stats values(*) as * by <common_field_name>, because neither of the searches seem to have any common field names. I've tried using both "Usage" and "Usage for Each Database" as the <common_field_name>, and although the results are different, it's not what we want.

How can I add "Usage for Each Database" field to the first search in order for both searches to have a common field?

spk-append1.PNGspk-append2.PNG

0 Karma

richgalloway
SplunkTrust
SplunkTrust

There really needs to be something to tie the two searches together.  If there isn't then perhaps there is a third search you can run to link the data.  Note that having a common field name is just part of the battle - it's more important to have shared field *values*.  After all, one can rename any field to match another, but if the values don't match then the effect is nil.

---
If this reply helps you, an upvote would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

You can try search1 | appendcols [search2], but that requires both searches to return results in the exact same order and quantity.  A more reliable method is search1 | append [search2] | stats values(*) as * by someCommonFieldName, but that requires both searches to share a field name which can be used to match the results from each search.

---
If this reply helps you, an upvote would be appreciated.

mxanareckless
Path Finder

@richgalloway 

Thanks for your reply. I've tried your second suggested method.

Here's the exact results of the subseach, which I would like appended as a column to the result table of the main search:

dbbakreport4.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I've tried putting this into an | append in the main search, but I see only a blank row added to the table.

dbbakreport5.PNG

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.