How to remove redudant data from a query

[azulgrana]

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!

[t_shreya · Accepted solution]

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

[azulgrana]

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

Cheers

AzulgranaPDX