Add a blank row in the table

[Navanitha]

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

[jkat54]

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

[Navanitha]

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

[jkat54]

How about you share your search then?

[Navanitha]

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 | addcoltotals labelfield=DATE label="count btwn 10h and 12h"

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.

[somesoni2 · Accepted solution]

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"

[Navanitha]

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.

[somesoni2]

Give the updated search a try

[Nextbeat]

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.

[somesoni2]

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

[Nextbeat]

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

[somesoni2]

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

[Nextbeat]

Good to know thanks again for the knowledge.

[Navanitha]

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

[somesoni2]

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.

[somesoni2]

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