Reporting

Remove fields with same department

kcchu01
Explorer

We have developed a query to list out all the user accounts that login from a particular IP address, the table is as follows.

IP             Username      Department
xxx.xxx.xxx      abc123          Dept A
                 def456          Dept B
                 efg789          Dept C
--------------------------------------------------------------------
yyy.yyy.yyy      xyz123          Dept A
                 zxc456          Dept A
--------------------------------------------------------------------
zzz.zzz.zzz      aaa234          Dept A
                 bbb456          Dept A
                 ccc123          Dept C

What we want to enhance the case now is removing the entry that with same department, that means the new table becomes

IP              Username     Department
xxx.xxx.xxx       abc123         Dept A
                  def456         Dept B
                  efg789         Dept C
--------------------------------------------------------------------
zzz.zzz.zzz       aaa234         Dept A
                  bbb456         Dept A
                  ccc123         Dept C

I have no idea on this. Anyone please help.

Tags (1)
0 Karma

somesoni2
Revered Legend

Try this

your current search giving fields  IP , Username ,Department | where mvcount(mvdedup(Department))>1
0 Karma

woodcock
Esteemed Legend

If I understand you correctly, you would like to throw away any entry which only has a single department in it. You can do that by adding this to your search:

... | eventstats dc(Department) AS numDepartments BY IP | where numDepartments>1 | fields - numDepartments

If that is now what you mean then your example, in combination with your text, makes no sense.

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

Could you post your current search?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...