Remove fields with same department

[kcchu01]

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.

[somesoni2]

Try this

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

[woodcock]

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.

[jluo_splunk]

Could you post your current search?