How to display null event result (fillnull)

antonio147 — Tue, 31 May 2022 14:06:41 GMT

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

Re: display null event result (fillnull)

lnn2204 — Tue, 31 May 2022 08:40:55 GMT

Did you try fillnull before stats sum command?

Re: display null event result (fillnull)

antonio147 — Tue, 31 May 2022 08:48:37 GMT

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

Re: display null event result (fillnull)

ITWhisperer — Tue, 31 May 2022 08:52:29 GMT

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

Re: display null event result (fillnull)

antonio147 — Tue, 31 May 2022 09:33:08 GMT

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.

topic Re: display null event result (fillnull) in Splunk Search

How to display null event result (fillnull)

Re: display null event result (fillnull)

Re: display null event result (fillnull)

Re: display null event result (fillnull)

Re: display null event result (fillnull)