Dedup when some fileds are empty

[rkeq0515]

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.

[manjunathmeti]

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

[to4kawa]

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

[anmolpatel · Accepted solution]

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

[rkeq0515]

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