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