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!

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