Splunk Search

Add a blank row in the table

Navanitha
Explorer

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

Tags (3)
0 Karma
1 Solution

somesoni2
Revered Legend

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"

View solution in original post

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

Navanitha
Explorer

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

How about you share your search then?

0 Karma

Navanitha
Explorer

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.

0 Karma

somesoni2
Revered Legend

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"

View solution in original post

Navanitha
Explorer

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.

0 Karma

somesoni2
Revered Legend

Give the updated search a try

0 Karma

Nextbeat
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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
Path Finder

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

0 Karma

somesoni2
Revered Legend

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
Path Finder

Good to know thanks again for the knowledge.

0 Karma

Navanitha
Explorer

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

0 Karma

somesoni2
Revered Legend

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.

0 Karma

somesoni2
Revered Legend

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!