How to display null event result (fillnull)

[antonio147]

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

[ITWhisperer · Accepted solution]

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]

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]

Did you try fillnull before stats sum command?

[antonio147]

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