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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...