topic Re: how to get a customized table in Splunk Search

how to get a customized table

Ash1 — Fri, 20 Oct 2023 17:58:27 GMT

I have a query and i need to show the logs as shown in the below image.

Total Messages: index=app-logs " Request received from all applications" |stats count

Error count: Sum of count (App logs + Exception logs + Canceled logs + 401 mess logs)
App logs: index=app-logs "Application logs received"
Exception logs: index=app-logs "Exception logs received"
Canceled logs: index=app-logs "unpassed logs received"
401 mess logs: index=app-logs "401 error message"

Stand by count: Subtract(url - cleared log)
url: index=app-logs "url info staged"
cleared log: index=app-logs "Filtered logs arranged"

Re: how to get a customized table

PickleRick — Fri, 20 Oct 2023 20:18:54 GMT

And what have you tried so far and what is the problem with your result?

To make things clear - in Splunk there is no "merging" of cells. Maybe there is a visualization which silently renders a table this way but I know of no such thing. Generally, a table has a "full grid" of results.

Do you have problems with combining your searches into a single one or do you have the search but can't visualize it?

Re: how to get a customized table

Ash1 — Fri, 20 Oct 2023 23:56:13 GMT

Hi @PickleRick , i tried the below query but this is showing as normal table, i am not getting in the way i showed in the image.
I just want to know whether that is doable in Splunk ???
if yes how can i tweak my query????

|tstats count as Total_Messages where index=app-logs TERM(Request) TERM(received) TERM(from) TERM(all) TERM(applications)
|appendcols
[|tstats count as App_logs where index=app-logs TERM(Application) TERM(logs) TERM(received)]
|appendcols
[|tstats count as Exception_logs where index=app-logs TERM(Exception) TERM(logs) TERM(received)
|appendcols
[|tstats count as Canceled_logs where index=app-logs TERM(unpassed) TERM( logs) TERM(received)]

|appendcols
[|tstats count as 401_mess_logs where index=app-logs TERM(401) TERM( error) TERM(message)]

|appendcols
[|tstats count as url where index=app-logs TERM(url) TERM( info) TERM(staged)]

|appendcols
[|tstats count as cleared_log where index=app-logs TERM(Filtered) TERM(logs) TERM(arranged)]
|table *

Re: how to get a customized table

ITWhisperer — Sat, 21 Oct 2023 09:46:30 GMT

| makeresults format=csv data="group,log,count Error,App logs,100 Error,Exception logs,100 Error,Cancelled logs,25 Error,401 mess logs,25 Stand by,url,150 Stand by,cleared log,100" ``` The previous lines set up some sample data in line with your image ``` | appendpipe [| stats sum(count) as total by group] | sort 0 -group count log | addcoltotals labelfield=group | eval count=coalesce(count,total) | eval summary=if(isnull(log),group." count",null()) | eval group=if(isnull(log),null(),group) | reverse | table summary total group log count

Re: how to get a customized table

yuanliu — Sat, 21 Oct 2023 10:14:48 GMT

As @PickleRick said, Splunk does not mimic modern spreadsheet's visualization. The forte of Splunk is to turn unstructured data into relational tables. Every grid in Splunk is fully rendered. Text alignment is not articulated. And cell coloring is generally unsupported.

With these constraints, you can design your own visual vocabulary to render the cells with various elements. For example, your spreadsheet visualization can be simulated with

Note your illustrated Standby count of 250 is the sum of url and cleared_log, not the difference as you formulated. I suspect that this is intended. So, I added an additional visual element under breakdowns to highlight the url - cleared_log.

The above is rendered with the following search

| tstats count as App_logs where index=app-logs TERM(Application) TERM(logs) TERM(received) | appendcols [|tstats count as Exception_logs where index=app-logs TERM(Exception) TERM(logs) TERM(received)] | appendcols [|tstats count as Canceled_logs where index=app-logs TERM(unpassed) TERM( logs) TERM(received)] | appendcols [|tstats count as 401_mess_logs where index=app-logs TERM(401) TERM( error) TERM(message)] | eval mess_type = "Error count", count = App_logs + Exception_logs + Canceled_logs + '401_mess_logs' | eval breakdowns = mvappend("App_logs: " . App_logs, "Exception_logs: " . Exception_logs, "Canceled_logs: " . Canceled_logs, "401_mess_logs: " . '401_mess_logs') | fields - *_logs | append [|tstats count as url where index=app-logs TERM(url) TERM( info) TERM(staged) |appendcols [|tstats count as cleared_log where index=app-logs TERM(Filtered) TERM(logs) TERM(arranged)] | eval mess_type = "Standby count", count = url + cleared_log | eval breakdowns = mvappend("url: " . url, "cleared_log: " . cleared_log, ":standby: " . (url - cleared_log)) | fields - url cleared_log] | addcoltotals labelfield=mess_type label="Total mess" | table mess_type count breakdowns

Note: I did not change your tstats searches. If the TERM combinations give you the correct counts, great. If not, you may need to use index searches. In that scenario, append and appendcols are so inefficient you will need to use other methods to get individual counts. But the visual tweaks remain the same.

Hope this helps.

Re: how to get a customized table

Ash1 — Wed, 25 Oct 2023 15:56:32 GMT

Hi @PickleRick , Thank you so much i got the query what i was expecting.
But i have some changes here can you help me on that

Standby Column i don't have any individual searches like url and cleared_log i just need standby count as Toatal _Messages - Errro_count should be displyed on the table. Rest all will be the same.

|tstats count as Total_Messages where index=app-logs TERM(Request) TERM(received) TERM(from) TERM(all) TERM(applications)