Splunk Search

showing table despite no results

lostcauz3
Path Finder

 

TYPEMonthKPI_1KPI_2
GLOBALOct'217624
LOCALOct'214667

 

I'm searching the table like | search TYPE="GLOBAL" | search Month="Oct'21"

Then i'm transposing the table after  deleting the months field

| fields - Month | transpose header_field=TYPE column_name=KPI

 My problem is sometimes when I'm searching something that is not there like Month="Sep'21"

only the first column of the transposed table is coming

like

KPI
KPI_1
KPI_2


How to show no results found instead of this 1 column table

Labels (3)
0 Karma

PickleRick
Champion
| foreach * 
[ eval r=if(<<FIELD>>!="kpi",coalesce(r,<<FIELD>>),r)]
| where r!=kpi
| fields - r
0 Karma

yuanliu
SplunkTrust
SplunkTrust

You may want to post sanitized code to show how different conditions cause the first column to remain when no data return from searches and filters.

In my testing, the only way the first column after transpose remain when filters  return no data is if the filter like Month="Sep'21" is positioned AFTER the filter to remove Month from the table.  The following table illustrates two code examples that produce different outputs after transpose.  The first one is your desired outcome, the second the erroneous outcome.

1. No table

 2. Table with first column

| search Type=Global
| search Month="Sept'21"

| fields - Month

| transpose header_field=TYPE column_name=KPI

| search Type=Global

| fields - Month

| search Month="Sept'21"
| transpose header_field=TYPE column_name=KPI

I do not know what causes the second code to show a first column, but the second code is incorrect in that searching for a field that is explicitly dropped is pointless.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!