topic Re: how to format results getting from multiple append in Splunk Search

How to format results getting from multiple append?

MrIncredible — Fri, 06 Jan 2023 18:03:29 GMT

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

Expected Result: 2 columns * 3 rows

Bundle Count

Input x

Error x

Processed x

Re: how to format results getting from multiple append

PickleRick — Fri, 06 Jan 2023 11:39:21 GMT

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

Input	Processed	Bundle
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 🙂

Re: how to format results getting from multiple append

MrIncredible — Fri, 06 Jan 2023 12:17:20 GMT

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

TIA

Re: how to format results getting from multiple append

PickleRick — Fri, 06 Jan 2023 13:17:30 GMT

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.

Re: how to format results getting from multiple append

MrIncredible — Fri, 06 Jan 2023 13:19:20 GMT

Thanks very much @PickleRick for explanation.