Splunk Search
Highlighted

How to create a table row with all values?

Engager

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@vbotnari1

Try

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

View solution in original post

Highlighted

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

Engager

@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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@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

0 Karma
Highlighted

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

Engager

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

0 Karma