topic Re: Add a blank row in the table in Splunk Search

Add a blank row in the table

Navanitha — Fri, 06 May 2016 14:28:17 GMT

I have a search which would give me a table of results and at the end the total count of columns. I want a blank line to be added between my tabular results and the total count(count of col1). Is there a way that I can do it?

eg:

         col 1  col2 col3  col4
          12       23    2.4     4.4
          11       10    6.6      2.3

*blank line*********
Count 23

Re: Add a blank row in the table

somesoni2 — Fri, 06 May 2016 14:57:13 GMT

Try like this

your current search giving above output WITHOUT total count | append [| gentimes start=-1 | eval col1="" | table col1] | addcoltotals

Update
This should do the trick

index=_internal host=sk* sourcetype=splunkd* | chart count over sourcetype by log_level | appendpipe [| head 1 | foreach * [eval <<FIELD>>=""]] | addcoltotals labelfield=sourcetype

With your search

index=internal "|05|" (Text="successful" OR Text="Failed") date_hour>=10 AND date_hour<=12 
| bucket _time span=1h
| stats count(Text) as Count by _time
| JOIN _time [search index=internal "|05|" (Text="successful" OR Text="Failed") (date_hour>=10 AND date_hour<=12) 
| bucket _time span=1h
| timechart span=1h avg(time) by Text
| addtotals] |eval DATE=_time | convert timeformat="%Y-%m-%d %Hh" ctime(DATE) | table DATE, Count, "successful", "Failed" Total 
| appendpipe [| head 1 | foreach * [eval <<FIELD>>=""]] 
| addcoltotals labelfield=DATE label="count btwn 10h and 12h"

Re: Add a blank row in the table

Navanitha — Fri, 06 May 2016 15:04:42 GMT

Hey,

It is giving me total, but I want to add a blank line between the tabular data and the total count I am getting just to make my report look pretty.

Re: Add a blank row in the table

jkat54 — Sat, 07 May 2016 01:25:33 GMT

Let's pretend that host is on your x-axis

 Host1   12   23   2.4   4.4
 Host2   11   10   6.6   2.3

You'll then need to eval a new host '|append [search | head 1 | eval host=" "] | sort - host'. Or similar

Re: Add a blank row in the table

Navanitha — Mon, 09 May 2016 08:07:59 GMT

This is adding a blank column host. I want to add a blank row.

Re: Add a blank row in the table

jkat54 — Mon, 09 May 2016 10:42:02 GMT

How about you share your search then?

Re: Add a blank row in the table

Navanitha — Tue, 29 Sep 2020 09:37:53 GMT

Output will be below col's(labelling it as 1)
DATE Count Successful Failed Total

and the last row would be (labelling it as 2)
count btwn 10h and 12h Count Successful Failed Total

Now I want a blank row (just for formatting sake) between 1 & 2.

Re: Add a blank row in the table

somesoni2 — Mon, 09 May 2016 16:02:49 GMT

Give the updated search a try

Re: Add a blank row in the table

Navanitha — Mon, 09 May 2016 16:06:39 GMT

awesome this is what I was looking for. Thanks a lot. btw can you pls explain me the search from | appendpipe.

Re: Add a blank row in the table

somesoni2 — Mon, 09 May 2016 16:09:54 GMT

The appendpipe will run some aggregation command on the current resultset and add it to current result, so what I'm doing is taking just one row (placeholder so you can use 'head 1' OR 'tail 1'), using foreach, making every column value blank.

Re: Add a blank row in the table

somesoni2 — Mon, 09 May 2016 16:39:53 GMT

If there are no follow-up question, you can mark the question as answered.

Re: Add a blank row in the table

Nextbeat — Fri, 27 Apr 2018 14:01:54 GMT

Thanks for the solution! Since I had _time as part of the table, I had to add an eval statement to wipe its value. The suggested code wiped all of the fields except _time for some reason. I used the following code:

<search> | appendpipe [| head 1 | foreach * [eval <<FIELD>>=""] | eval _time=""] | append [<search>]

This allowed me to separate results for the initial search from the appended search.

Re: Add a blank row in the table

somesoni2 — Fri, 27 Apr 2018 14:52:49 GMT

I actually forgot about _time field. To fix that, your method would work fine OR you can change foreach from | head 1 | foreach * [eval... to | head 1 | foreach * _* [eval....

Re: Add a blank row in the table

Nextbeat — Fri, 27 Apr 2018 16:06:11 GMT

Thanks for the update! I would've thought the wildcard would've covered all the bases.

Re: Add a blank row in the table

somesoni2 — Fri, 27 Apr 2018 16:16:22 GMT

Actually just the wildcard covers all non-internal stuff. Anything starting with underscore are considered Splunk's internal/default stuff and needs explicit _* wildcarding. (e.g. if you run index=*, you won't get data from any of internal indexes such as _internal _audit etc).

Re: Add a blank row in the table

Nextbeat — Sun, 29 Apr 2018 02:25:39 GMT

Good to know thanks again for the knowledge.