Splunk Search

How to display null event result (fillnull)

antonio147
Communicator

Hi,
I have an event display problem when no events matching the conditions are found.
I want to filter only those events that have the "DATA_LAVORAZIONE" (STC) field greater than "OGGI" up to 7 days ahead.
In the AMPLIAMENTI sourcetype there are some events for which it returns the sum, while in the DIRETTA sourcetype there are no events, and it does not show me anything.
I would like the row with all 0s to be displayed anyway.
I tried with fillnull value = 0 field, field, field .... but it doesn't work.
Also tried fulldown, but nothing.
Do you have any suggestions?
Thank you

 

CODE:

index =DATI sourcetype = AMPLIAMENTI
|fields - _*
|eval OGGI=strftime(relative_time(now(),"-0d@d"), "%Y-%m-%d")
|eval OGGI_1=strftime(relative_time(now(),"+1d@d"), "%Y-%m-%d")
|eval OGGI_2=strftime(relative_time(now(),"+2d@d"), "%Y-%m-%d")
|eval OGGI_3=strftime(relative_time(now(),"+3d@d"), "%Y-%m-%d")
|eval OGGI_4=strftime(relative_time(now(),"+4d@d"), "%Y-%m-%d")
|eval OGGI_5=strftime(relative_time(now(),"+5d@d"), "%Y-%m-%d")
|eval OGGI_6=strftime(relative_time(now(),"+6d@d"), "%Y-%m-%d")
|eval OGGI_7=strftime(relative_time(now(),"+7d@d"), "%Y-%m-%d")
|eval STC=strftime(strptime(DATA_LAVORAZIONE, "%Y-%m-%d"), "%Y-%m-%d")
|where STC > OGGI
|eval X = if(STC=OGGI,1,0)
|eval X+1 = if(STC=OGGI_1,1,0)
|eval X+2 = if(STC=OGGI_2,1,0)
|eval X+3 = if(STC=OGGI_3,1,0)
|eval X+4 = if(STC=OGGI_4,1,0)
|eval X+5 = if(STC=OGGI_5,1,0)
|eval X+6 = if(STC=OGGI_6,1,0)
|eval X+7 = if(STC=OGGI_7,1,0)

|eval TOTALE=if(STC > OGGI AND STC <= OGGI_7,1,0)
|eval TUTTI=if(STC > OGGI ,1,0)
|sort - DATE_UPD, LINK
|dedup LINK
|where STATO IN("LAVORAZIONE", "CONFERMA DATA")

|stats sum(X) as X, sum(X+1) as X+1,sum(X+2) as X+2, sum(X+3) as X+3,sum(X+4) as X+4,sum(X+5) as X+5, sum(X+6) as X+6,sum(X+7) as X+7, sum(TOTALE) as TOTALE,sum(TUTTI) as OVER
|eval TIPOL ="AMPLIAMENTI"
|table TIPOL X X+1 X+2 X+3 X+4 X+5 X+6 X+7  TOTALE OVER

|append [ search index =DATI sourcetype = diretta
|fields - _*
|where TIPOLOGIA IN("SUBNET","VOCE")
|eval OGGI=strftime(relative_time(now(),"-0d@d"), "%Y-%m-%d")
|eval OGGI_1=strftime(relative_time(now(),"+1d@d"), "%Y-%m-%d")
|eval OGGI_2=strftime(relative_time(now(),"+2d@d"), "%Y-%m-%d")
|eval OGGI_3=strftime(relative_time(now(),"+3d@d"), "%Y-%m-%d")
|eval OGGI_4=strftime(relative_time(now(),"+4d@d"), "%Y-%m-%d")
|eval OGGI_5=strftime(relative_time(now(),"+5d@d"), "%Y-%m-%d")
|eval OGGI_6=strftime(relative_time(now(),"+6d@d"), "%Y-%m-%d")
|eval OGGI_7=strftime(relative_time(now(),"+7d@d"), "%Y-%m-%d")
|eval STC=strftime(strptime(DATA_LAVORAZIONE, "%Y-%m-%d"), "%Y-%m-%d")
|where STC > OGGI
|eval X = if(STC=OGGI,1,0)
|eval X+1 = if(STC=OGGI_1,1,0)
|eval X+2 = if(STC=OGGI_2,1,0)
|eval X+3 = if(STC=OGGI_3,1,0)
|eval X+4 = if(STC=OGGI_4,1,0)
|eval X+5 = if(STC=OGGI_5,1,0)
|eval X+6 = if(STC=OGGI_6,1,0)
|eval X+7 = if(STC=OGGI_7,1,0)
|eval TOTALE=if(STC > OGGI AND STC <= OGGI_7,1,0)
|eval TUTTI=if(STC > OGGI ,1,0)
|sort - DATE_UPD, LINK
|dedup LINK
|where STATO IN("CONFERMA DATA")
|stats sum(X) as X, sum(X+1) as X+1,sum(X+2) as X+2, sum(X+3) as X+3,sum(X+4) as X+4,sum(X+5) as X+5, sum(X+6) as X+6,sum(X+7) as X+7, sum(TOTALE) as TOTALE,sum(TUTTI) as OVER
|eval TIPOL ="SUBNET  - VOCE"
| fillnull value=0 TIPOL X X+1 X+2 X+3 X+4 X+5 X+6 X+7 TOTALE OVER
|table TIPOL X X+1 X+2 X+3 X+4 X+5 X+6 X+7  TOTALE OVER]

. (others APPEND)

.

.

|table TIPOL X X+1 X+2 X+3 X+4 X+5 X+6 X+7  TOTALE OVER

 

RESULT:

TIPOL                                  X    X+1   X+2    X+3 ........ TOTAL   OVER

AMPLIAMENTI                0       2       1          0     .......       3            3

 

DESIRED: 

TIPOL                                  X    X+1   X+2    X+3 ........ TOTAL   OVER

AMPLIAMENTI                0       2       1          0     .......       3            3

SUBNET - VOCE             0      0        0          0  .........       0            0

TKS

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Assuming your field names don't really have + signs in, try something like this:

|stats sum(X) as X, sum(X+1) as X+1,sum(X+2) as X+2, sum(X+3) as X+3,sum(X+4) as X+4,sum(X+5) as X+5, sum(X+6) as X+6,sum(X+7) as X+7, sum(TOTALE) as TOTALE,sum(TUTTI) as OVER
| appendpipe [stats count as _count
              | where _count = 0
              | eval X = 0, X+1 = 0, X+2 = 0, X+3 = 0, X+4 = 0, X+5 = 0, X+6 = 0, X+7 = 0, TOTALE = 0, OVER = 0]
|eval TIPOL ="AMPLIAMENTI"

Similarly for other values of TIPOL

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Assuming your field names don't really have + signs in, try something like this:

|stats sum(X) as X, sum(X+1) as X+1,sum(X+2) as X+2, sum(X+3) as X+3,sum(X+4) as X+4,sum(X+5) as X+5, sum(X+6) as X+6,sum(X+7) as X+7, sum(TOTALE) as TOTALE,sum(TUTTI) as OVER
| appendpipe [stats count as _count
              | where _count = 0
              | eval X = 0, X+1 = 0, X+2 = 0, X+3 = 0, X+4 = 0, X+5 = 0, X+6 = 0, X+7 = 0, TOTALE = 0, OVER = 0]
|eval TIPOL ="AMPLIAMENTI"

Similarly for other values of TIPOL

antonio147
Communicator

Hi IT Whisperer,
you are the best !!!
Great was just what I needed and I couldn't do.
Thank you so much for your great help.

lnn2204
Path Finder

Did you try fillnull before stats sum command?

0 Karma

antonio147
Communicator

Hi,
I tried your suggestion, putting fillnull before stats, but nothing, no result, the line doesn't show.
Tks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...