topic Re: How do you add a blank row after each unique host in search results? in Splunk Search

How do you add a blank row after each unique host in search results?

kenntun — Mon, 14 Jan 2019 04:26:17 GMT

I have a search statement in a customized dashboard to show the disk utilization of my servers. I would like to add a blank row after each unique server, such as follows:

Current:

host          mount     Disk size(GB)    Free(GB)   
host1        /            5.0           3.0 
host1        /etc          5.0           2.4
host2        /             10.0         4.0 
host2        /etc          20.0        14.0
host2        /var          15.0        8.9
host3        /            15.0        6.0 
host3        /mnt         15.0        10.3

What I wanted:

host          mount     Disk size(GB)    Free(GB)   
host1        /            5.0           3.0 
host1        /etc          5.0           2.4

host2        /             10.0         4.0 
host2        /etc          20.0        14.0
host2        /var          15.0        8.9

host3        /            15.0        6.0 
host3        /mnt         15.0        10.3

Note: Not all servers have the same number of mounts.

My search statement:

| tstats latest(JFS.storage) AS storage, latest(JFS.storage_free) AS storage_free, latest(JFS.storage_used) AS storage_used, latest(JFS.storage_used_percent) AS storage_used_percent from datamodel=NMON_Data_JFS
where (nodename = JFS.DF_STORAGE) (host=$host-prefilter$) ($frameID$) ($osfilter$) ($host$) ($mount$) (JFS.mount=$fsfilter$) groupby host JFS.mount prestats=true
| stats dedup_splitvals=t latest(JFS.storage) AS storage, latest(JFS.storage_free) AS storage_free, latest(JFS.storage_used) AS storage_used, latest(JFS.storage_used_percent) AS storage_used_percent by host JFS.mount
| sort limit=0 host
| rename "JFS.mount" AS "mount"
| fields host, mount, storage, storage_free,storage_used,storage_used_percent
| foreach storage storage_free storage_used [ eval <<FIELD>> = round(('<<FIELD>>'$df_storage_unit_math$), 2) ]
| rename storage as "Disk Size ($df_storage_unit_legend$)", storage_free as "Free ($df_storage_unit_legend$)", storage_used as "Used ($df_storage_unit_legend$)", storage_used_percent as "Used (%)"
| eval UsedPct=if(isnum('Used (%)'), 'Used (%)', 0 )
| fields host, mount, "Disk Size ($df_storage_unit_legend$)", "Free ($df_storage_unit_legend$)", "Used ($df_storage_unit_legend$)", "Used (%)"
| eval "Used (%)" = if(isnull('storage used (%)'), (('Used ($df_storage_unit_legend$)'/'Disk Size ($df_storage_unit_legend$)')*100), 'Used (%)')
| foreach storage*%* [ eval <<FIELD>> = round('<<FIELD>>', 2) ]

Re: How do you add a blank row after each unique host in search results?

shrikantgulia1 — Mon, 14 Jan 2019 05:16:46 GMT

Hello,
Please look.... this may be of some use

https://answers.splunk.com/answers/399417/add-a-blank-row-in-the-table.html

Regards

Re: How do you add a blank row after each unique host in search results?

shrikantgulia1 — Mon, 14 Jan 2019 05:17:43 GMT

you can also use fillnull

Re: How do you add a blank row after each unique host in search results?

kenntun — Mon, 14 Jan 2019 05:48:31 GMT

Could you elaborate more? Thanks a lot

Re: How do you add a blank row after each unique host in search results?

kenntun — Mon, 14 Jan 2019 08:56:17 GMT

Thanks for your help.
However, the situation is a bit different since the post only have to add one line in the second row, but I want to add a single line every time the value of the first column is different. Any suggestions?

Re: How do you add a blank row after each unique host in search results?

kenntun — Mon, 14 Jan 2019 09:07:48 GMT

Any methods to compare string values of two different rows with something like an if -else statement?

Re: How do you add a blank row after each unique host in search results?

shrikantgulia1 — Mon, 14 Jan 2019 09:10:20 GMT

fillnull value="as" test,

this is used when you dont have any vale in a field and you give it a value

Re: How do you add a blank row after each unique host in search results?

kenntun — Mon, 14 Jan 2019 09:39:49 GMT

I think you misunderstood my situation. There are no blank fields in my search results.
I've edited the question. Thanks again.

Re: How do you add a blank row after each unique host in search results?

askkawalkar — Mon, 14 Jan 2019 10:52:45 GMT

Hi @kenntun ,

I have used above data as input and loaded it into Splunk. Below is one possible solution. I hope this solution can help you.
P.S.: I have used sorting on the basis of hostname and mount and accordingly.

| makeresults 
 | eval data="host1,x, , ;host2,x, , ;host3,x, , " 
 | makemv data delim=";" 
 | mvexpand data 
 | makemv data delim="," 
| eval hostval=mvindex(data,0),mount=mvindex(data,1),Disksize=mvindex(data,2),Free=mvindex(data,3)
| table hostval,mount,Disksize,Free
| append
    [search index=test source="C:\\Splunk_Data\\Test\\testdata_splunk.csv"
| dedup hostval,mount
| table hostval,mount,Disksize,Free
]
| sort hostval,mount
| eval hostval=if(mount="x","",hostval),mount=if(mount="x"," ",mount)