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

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
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...