Splunk Search

How to fill null value of multi value fields with other value in search output

Explorer

Hello Community,

I need to fill null value of multi-field values with any value , i.e 0 or Not found.

Here's the sample data in table

Sample Table

Customer_Id           Counter_ID    Customer_Name               Desk_ID        Purchased_Item

     121                                                            1                     Pen

     121                                                            1                     Pencil

Expected Output

 Customer_Id      Counter_ID        Customer_Name     Desk_ID         Purchased_Item

   121                  0                     0              1                       Pen

   121                   0                     0              1                      Pencil

current Output

 Customer_Id        Counter_ID      Customer_Name        Desk_ID           Purchased_Item

   121                       0                0             1                                 Pen

                                                            1                                Pencil

Where what i wrote so far.

| stats list(Customer_Id) as Customer_id, stats list(Counter _Id) as Counter _id, stats list(Customer_Name) as Customer_Name,

stats list(Desk_ID) as Desk_ID, stats list(Purchased_Item) as Purchased_Item By Customer_Id | fillnull value=0 Counter_ID Customer_Name

Not sure how to proceed from here. Tried using foreach. But can't get what i want.

Any help would be really appreciated.

0 Karma
1 Solution

Ultra Champion
 ... | stats ...
| eval null_fill = mvrange(0,mvcount(Customer_Id))
| rex field=null_fill mode=sed "s/\d+/NA/g"
| eval Counter_ID=mvappend(Counter_ID,null_fill)
| eval Customer_Name = mvappend(Customer_Name,null_fill)
| eval Counter_ID=mvindex(Counter_ID,0,mvcount(Customer_Id))
| eval Customer_Name =mvindex(Customer_Name,0,mvcount(Customer_Id))
| table  Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item

maybe works.

View solution in original post

Builder

Hi,

Can try to use fillnull befor stats command, like this:

| makeresults count=1
     | eval data = "121 1 Pen;121 1 Pencil"
     | makemv delim=";" data
     | mvexpand data
     | rex field=data "(?<Customer_Id>[^\s]+)\s(?<Desk_ID>\d+)\s(?<Purchased_Item>\w+)"
     | table Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item
     | fillnull Counter_ID Customer_Name value=0
     | stats list(Counter_ID) as Counter_ID, list(Customer_Name) as Customer_Name, list(Desk_ID) as Desk_ID, list(Purchased_Item) as Purchased_Item by Customer_Id

Smile Splunk 🙂

0 Karma

Ultra Champion
 ... | stats ...
| eval null_fill = mvrange(0,mvcount(Customer_Id))
| rex field=null_fill mode=sed "s/\d+/NA/g"
| eval Counter_ID=mvappend(Counter_ID,null_fill)
| eval Customer_Name = mvappend(Customer_Name,null_fill)
| eval Counter_ID=mvindex(Counter_ID,0,mvcount(Customer_Id))
| eval Customer_Name =mvindex(Customer_Name,0,mvcount(Customer_Id))
| table  Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item

maybe works.

View solution in original post

Explorer

works like a charm. All i needed to added if(isnull) check along with mvappend

eval Counter_ID=if(isnull(Counter_ID), mvappend(Counter_ID,null_fill),Counter_ID)

Thanks a lot @to4kawa

0 Karma

Ultra Champion

and ,you are Splunk Ninja 🙂

0 Karma

Explorer

@to4kawa hi, just wanted to follow up on this? While this works for the mentioned scenrio. but it doesn't work if there is already a value. Like this,

Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item

  121                              1                             1                     Pen

  121                                                            1                     Pencil

Any idea what to do, if i want to fill up the vacant counter_id with some value? Would really appreciate the help.

0 Karma

Ultra Champion

filldown and fillnull , maybe.

0 Karma

Explorer

before that stats command?

0 Karma

Ultra Champion

after stats

0 Karma

Explorer

thanks for the tips. Will let you know if it works.

0 Karma

Ultra Champion
| makeresults
| eval _raw="Customer_Id,Counter_ID,Customer_Name,Desk_ID,Purchased_Item
121,1,,1,Pen
121,,,1,Pencil"
| multikv forceheader=1
| table Customer_Id,Counter_ID,Customer_Name,Desk_ID,Purchased_Item
| filldown
| fillnull Counter_ID,Customer_Name,Desk_ID value="N/A"

e.g.

Explorer

@to4kawa Thanks a lot.

0 Karma

Explorer

🙂 thanks again.

0 Karma

Ultra Champion

your previous question

| eval Counter_ID = if(source = "A" AND isnull(Counter_ID), "N/A", Counter_ID)
| eval Customer_Name = if(source = "A" AND isnull(Customer_Name), "N/A", Customer_Name)
| stats . . . 

before stats, you should fill null values.

0 Karma

Explorer

@to4kawa thanks. I will let you know which one works once i get to test them.

0 Karma

SplunkTrust
SplunkTrust

Hi @sharif_ahmmad,

If I understand your query correctly then replacing your entire stats statement with this would give you the result you're looking for :

... | table  Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item
    | fillnull value=0 

This would work because all you're trying to do is list the info, not trying to do any real aggregation.

The above search won't run fast though, so you can improve it by using something along those lines :

... | stats  values(Counter _Id) as Counter _id  values(Customer_Name) as Customer_Name values(Desk_ID) as Desk_ID  By Customer_Id,Purchased_Item
   | fillnull value=0 

This will include some multi-value fields that you can break using | mvexpand fieldname.
Your final search should look something like this :

... | stats  values(Counter _Id) as Counter _id  values(Customer_Name) as Customer_Name values(Desk_ID) as Desk_ID  By Customer_Id,Purchased_Item
   | fillnull value=0 
   | mvexpand whicheverFieldHasMultiValue

Let me know if this helps.

Cheers,
David

Explorer

Hi @DavidHourani thanks. i like the idea of mvexpanding. Will let you know if it works.

But in the mean time, Can we use any other option than stats values() or table command ? Because stats values() will break the natural event order of log. no? And in this scenario table command will not put all the relevant values in the same row. I forgot to mention, for this case Desk_ID & Purchased_Item belongs to separate sourcetype than Counter _Id & Customer_Name.

0 Karma

SplunkTrust
SplunkTrust

Hi @sharif_ahmmad,

In case values() doesn't work for you, you can stick with list(). I used values() to avoid duplicated, but in your case I guess you need to keep the duplicates.

This point is crucial as this means that you must use aggregation to join the events :

I forgot to mention, for this case Desk_ID & Purchased_Item belongs to separate sourcetype than Counter _Id & Customer_Name.

Since the only common field in that case is Customer_Id then the search should actually be like this :

 ... | stats  list(Counter _Id) as Counter _id  list(Customer_Name) as Customer_Name list(Desk_ID) as Desk_ID  list(Purchased_Item) as Purchased_Item By Customer_Id
    |mvexpand Purchased_Item
    |mvexpand Desk_ID
    | fillnull value=0 

Let me know if this works any better for you.
Cheers,
David

0 Karma

Builder

Is the fillnull command not an option? You can fill either a specific field, i.e.:

| fillnull Counter_Id value=0

or a catch all fill null:

|fillnull value="N/A"

You can also do checks with the |where or |eval command for if things are null, and then filling them accordingly (a little more abstract for this use case but in general it's helpful to use sometimes) https://docs.splunk.com/Documentation/Splunk/8.0.1/SearchReference/InformationalFunctions#isnotnull....

Either:

| where isnotnull(fieldName)
or
|eval fieldName=if(isnull(fieldName), someAction, fieldName)

Hope this helps!

0 Karma

Explorer

Hi @aberkow , thanks. but if you see my shared query i already tried with fillnull value. But what it does is fill of the null value of first row multi valued fields. What i need also is the same thing for the second row, which i am having problem with.

0 Karma