Splunk Search

How to create a table row with all values?

vbotnari1
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
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@vbotnari1

Try

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

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@vbotnari1

Try

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

vbotnari1
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

renjith_nair
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

Happy Splunking!
0 Karma

vbotnari1
Engager

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...