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!

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