All Apps and Add-ons

Dedup when some fileds are empty

rkeq0515
Path Finder

I am attempting to display unique values in a table. Some of the fields are empty and some are populated with the respected data.

For example, I only want the following unique fields from each of the events:

systemname | domain | os
system1 | abc.com | Windows 10
system2 | | Windows 7
system3 | abc.com | Windows 10
system4 | abc.com | Windows 7
system1 | abc.com | Windows 10
system2 | | Windows 7
system3 | abc.com | Windows 10
system4 | abc.com | Windows 7

When I run the command:
| dedup systemname, domain, os | table systemname, domain, os

I get the following results:
system1 | abc.com | Windows 10
system3 | abc.com | Windows 10
system4 | abc.com | Windows 7

The desired result is:
system1 | abc.com | Windows 10
system2 | | Windows 7
system3 | abc.com | Windows 10
system4 | abc.com | Windows 7

It is not listing the data with the blank field. I tried various options with using the dedup command such as keepempty=true, but that is not working. I have also tried uniq, but my understanding is that compares the entire record, which is not what I want.

0 Karma
1 Solution

anmolpatel
Builder

This will give you the result. You just need to stats spl line from below:

| makeresults 
| eval _raw = "systemname, domain, os
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 7
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 7" 
| multikv forceheader=1
| stats values(domain) as domain values(os) as os by systemname

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

hi @rkeq0515,

Just do stats count by.

| stats count by systemname, os, domain | fields - count

Sample query:

| makeresults 
| eval _raw = " systemname, domain, os
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 10
system4, abc.com, Windows 7
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 7" 
| multikv forceheader=1
| stats count by systemname, os, domain | fields - count
0 Karma

to4kawa
Ultra Champion
your search and table
| fillnull
| dedup systemname, domain, os
| table systemname, domain, os
| foreach * [ eval <<FIELD>>=nullif('<<FIELD>>', 0) ]
0 Karma

anmolpatel
Builder

This will give you the result. You just need to stats spl line from below:

| makeresults 
| eval _raw = "systemname, domain, os
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 7
system1, abc.com, Windows 10
system2, , Windows 7
system3, abc.com, Windows 10
system4, abc.com, Windows 7" 
| multikv forceheader=1
| stats values(domain) as domain values(os) as os by systemname

View solution in original post

rkeq0515
Path Finder

This is what worked for me after a few adjustments.

| table systemname, domain, os | stats values(domain) as domain values(os) as os by systemname

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!