Splunk Search

How to remove redudant data from a query

azulgrana
Path Finder

Hi there!

I have a custom query that produces an output similar to this ...

 

 

| makeresults
| eval data= "Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Word;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Paint;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=VMWare Tools;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Word;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Paint;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=VMWare Tools;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table Name IP OS Software

 

 

 

2.png
My goal is to remove some of the redundant data on the output and produce something like this, where each software stills has its own row. 

3.png
* Image edited w/ Snagit

The reason why I'm looking into this is because I want the CSV export to look w/ the exact same format.

I have tried adding this to the query

 

 

| stats values(Software) as Software by Name, IP, OS

 

 

 
Which puts me closer to what I want, although when I export the data to CSV all the software show up under one cell which is fine when you have 2 or 3 but it is definitely a no go when you have +100 software / asset.

4.png

Any ideas?

TIA!

Labels (1)
0 Karma
1 Solution

t_shreya
Path Finder

Hi @azulgrana ,

Can you try the following?

The search is basically creating a new field which is a string joining the fields -  Name, IP and OS.
Streamstats command has been used to calculate the number of times that string is found in the data and if the count is more than one, then eval has been used to make the fields blank.

| makeresults
| eval data= "Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Word;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Paint;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=VMWare Tools;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Word;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Paint;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=VMWare Tools;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table Name IP OS Software
| eval dummy_value = Name."+".IP."+".OS
| streamstats count by dummy_value
| eval Name = if(count>1,"",Name)
| eval IP = if(count>1,"",IP)
| eval OS = if(count>1,"",OS)
| table Name IP OS Software


 I obtained the following result:Capture.PNG

View solution in original post

Tags (1)

t_shreya
Path Finder

Hi @azulgrana ,

Can you try the following?

The search is basically creating a new field which is a string joining the fields -  Name, IP and OS.
Streamstats command has been used to calculate the number of times that string is found in the data and if the count is more than one, then eval has been used to make the fields blank.

| makeresults
| eval data= "Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Word;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=Paint;Name=ServerA IP=1.1.1.1 OS=\"Windows 2016\" Software=VMWare Tools;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Word;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=Paint;Name=ServerB IP=1.1.1.2 OS=\"Windows 2016\" Software=VMWare Tools;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table Name IP OS Software
| eval dummy_value = Name."+".IP."+".OS
| streamstats count by dummy_value
| eval Name = if(count>1,"",Name)
| eval IP = if(count>1,"",IP)
| eval OS = if(count>1,"",OS)
| table Name IP OS Software


 I obtained the following result:Capture.PNG

Tags (1)

azulgrana
Path Finder

Nice!, thanks @t_shreya. I will test this in my environment

Cheers

AzulgranaPDX

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