Splunk Search

How to format results getting from multiple append?

MrIncredible
Explorer

Query:

index="web_app" (application= "abc-dxn-message-api" AND tracepoint= "START") (facility="d55075aaedc86d6577676605c0b5f3c0" OR "XYZ") | stats count as Input
|append [search (application= "hum-message-api" AND tracepoint= "END") (facility="d55075aaedc86d6577676605c0b5f3c0" OR "XYZ") | stats count as Processed]
|append [search (facility="d55075aaedc86d6577676605c0b5f3c0" OR "XYZ") "ERROR" | stats count as Error]
| transpose column_name="Bundle"

Current Result: 4 columns * 3 rows

MrIncredible_0-1673003404390.png

 

Expected Result: 2 columns * 3 rows

Bundle    Count

Input           x

Error            x

Processed x

Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

It's not "(*) AS *" but you need to take whole "values(*) AS *". It's a useful shorthand in splunk stats which works twofold.

Firstly - values(*) can be used to specify the same aggregation on all fields (or a subset of fields if you only match field names partially. This way you can avoid specifying multiple fields explicitly which makes the search easier to read and you can account for field names that you don't know the names beforehand. You just write values(*) and let splunk worry about the field names - you'll get a separate aggregation on each field that there is.

So that's one thing.

Secondly, we have wildcarded rename (the "AS" part). Without it we would get columns with names "values(Input)", "values(Processed)", and "values(Bundle)". We could live with that but it's not a pretty solution. So we apply the rename which - in case we use our stats with wildcard - can also take wildcard as argument which means that for each "source" column we will name the resulting column the same.

View solution in original post

MrIncredible
Explorer

Thanks very much @PickleRick for explanation.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

OK. Let me help you understand what's going on.

You have three separate searches which produce diffently named field in the output.

Just remove the final transpose from your command and you'll see.

You'll get something like

InputProcessedBundle
123  
 432 
  12

 

Which is perfectly understandable.

Now if you transpose it you just change the rows to columns and vice versa so you're getting a half-empty table.

What you need is to "compact" your results before calling transpose so you have just one row which after transposition will get to be one column.

So the easiest way is to do

| stats values(*) as *

That way you'll get just one row in your results with all the fields set by your appended subsearches. Now you can freely transpose this row if you wish 🙂

0 Karma

MrIncredible
Explorer

Thanks @PickleRick its working but do you mind if I ask you to explain this part a bit "stats values(*) as *" specially (*) as *.  🙂

 

TIA

0 Karma

PickleRick
SplunkTrust
SplunkTrust

It's not "(*) AS *" but you need to take whole "values(*) AS *". It's a useful shorthand in splunk stats which works twofold.

Firstly - values(*) can be used to specify the same aggregation on all fields (or a subset of fields if you only match field names partially. This way you can avoid specifying multiple fields explicitly which makes the search easier to read and you can account for field names that you don't know the names beforehand. You just write values(*) and let splunk worry about the field names - you'll get a separate aggregation on each field that there is.

So that's one thing.

Secondly, we have wildcarded rename (the "AS" part). Without it we would get columns with names "values(Input)", "values(Processed)", and "values(Bundle)". We could live with that but it's not a pretty solution. So we apply the rename which - in case we use our stats with wildcard - can also take wildcard as argument which means that for each "source" column we will name the resulting column the same.

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