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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...