topic Re: How to create a table row with all values? in Splunk Search

How to create a table row with all values?

vbotnari1 — Fri, 21 Jun 2019 11:11:38 GMT

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

Re: How to create a table row with all values?

renjith_nair — Fri, 21 Jun 2019 11:31:58 GMT

@vbotnari1

Try

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

Re: How to create a table row with all values?

vbotnari1 — Fri, 21 Jun 2019 11:51:21 GMT

@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

Re: How to create a table row with all values?

renjith_nair — Fri, 21 Jun 2019 12:23:57 GMT

@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

Re: How to create a table row with all values?

vbotnari1 — Fri, 21 Jun 2019 12:37:04 GMT

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