How to create a table row with all values?

[vbotnari1]

Hi

I have the following table:

IP             |      Event    |      Bad
10.10.10.1     |      fail     |
10.10.10.1     |               |      malicious

The result should look like 10.10.10.1 fail malicious.
I have to display the IP value only if it has both Event and bad values

In my search, an IP cannot have both Event and Bad values in the same row. I need to extract from the table if the same IP has both Event and Bad values in different rows and they display as I single row with all values. I hope this makes sense

[renjith_nair · Accepted solution]

@vbotnari1

Try

"your current search"
| stats values(Event) as Event,values(Bad) as Bad by IP
| where isnotnull(Event) AND isnotnull(Bad)

[vbotnari1]

@renjith.nair Thank you. But your search works for rows with both values.
In my search an IP cannot have both Event and Bad values in the same row. I need to extract from the table if the same IP has both Event and Bad values in different rows and them display as I single row with all values. I hope this makes sens

[renjith_nair]

@vbotnari1,
Yes the second line stats should do this for you. It picks up both values from events and group it with IP Isn't working ?

Here is the dummy search I used

| makeresults | eval IP="10.10.10.1 10.10.10.1 10.10.10.2 10.10.10.2"| makemv IP| mvexpand IP
| appendcols  [| makeresults | eval Event="Fail,,,Fail,," | makemv Event delim=","| mvexpand Event]
| appendcols [| makeresults| eval Bad=" ,malicious,,virus,"| makemv Bad delim=","| mvexpand Bad]
| table IP,Event,Bad | eval Bad=if(Bad==" ",null(),Bad)| eval Event=if(Event==" ",null(),Event)

and then the stats and where added to it

 | stats values(Event) as Event,values(Bad) as Bad by IP
 | where isnotnull(Event) AND isnotnull(Bad)

Please let me know if it's not matching with your dataset

[vbotnari1]

@renjith.nair Yes it works. Many thanks. Did a stupid spelling mistake.