Splunk Search

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

sharif_ahmmad
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

to4kawa
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

TISKAR
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

to4kawa
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.

haraksin
Path Finder

Fantastic answer; I made a simpler version without REX, since it is a compute hog, with test examples (if you get an error with the comment macro, just delete those lines):

| makeresults
`comment("Setting up fields to compare")`
| eval mv_more=mvappend("yes", "no", "not", "you"), mv_less=mvappend("maybe", "later")
`comment("Begin here")`
| eval null_fill = mvrange(mvcount(mv_less),mvcount(mv_more))
| eval null_fill=mvmap(null_fill, "N/A")
| eval mv_less_new = mvappend(mv_less,null_fill)

By taking the range from the length of the lesser field to the length of the greater field, we're able to eleminate a few eval statements crunching the difference with mvindex. You can try this by adding fields to mv_more; it will give you the same result each time.

sharif_ahmmad
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

to4kawa
Ultra Champion

and ,you are Splunk Ninja 🙂

0 Karma

sharif_ahmmad
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

to4kawa
Ultra Champion

filldown and fillnull , maybe.

0 Karma

sharif_ahmmad
Explorer

before that stats command?

0 Karma

to4kawa
Ultra Champion

after stats

0 Karma

sharif_ahmmad
Explorer

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

0 Karma

to4kawa
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.

sharif_ahmmad
Explorer

@to4kawa Thanks a lot.

0 Karma

sharif_ahmmad
Explorer

🙂 thanks again.

0 Karma

to4kawa
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

sharif_ahmmad
Explorer

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

0 Karma

DavidHourani
Super Champion

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

sharif_ahmmad
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

DavidHourani
Super Champion

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

aberkow
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

sharif_ahmmad
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
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 ...