topic Re: How to find the distinct value from the from two fields value ..... in Splunk Search

How to find the distinct value from the from two fields value .....

dilstn — Thu, 21 Mar 2013 11:19:52 GMT

I have two fields which have different timestamps ...so i need to identify the difference of the two values from it ....
i have generated regex and obtained the values for that fields ,,, and used table to display the values
The table look like this

CountryName Field1 Field2
Aus1345 Mar 21, 2013 9:46:55 AM
Sgp3785 Mar 21, 2013 9:43:34 AM
Can3452 Mar 21, 2013 9:42:13 AM
Are2904 Mar 21, 2013 9:37:08 AM
Pak2123 Mar 21, 2013 9:31:02 AM
Can4535 Mar 21, 2013 9:46:55 AM
Pak4653 Mar 21, 2013 9:46:55 AM
Sgp3785 Mar 21, 2013 9:43:34 AM
Aus1345 Mar 21, 2013 9:46:55 AM

Here this table contains duplicates like same countryname repeated so , i need to find only the countryname with respect to Field1 . so i need the countryname with Field1 (timings) alone ...
as given below ...... So eliminate the duplicate countryname by matching the timestamps of field1 and field2 ..... I need the countryname along with field1

I have identify this table by merging logs ......... so i need the countryname by finding difference between field1 and field2 ..... the difference of field1 and field2 with respect to field1 should be displayed with countryname ...as below ........

CountryName Field1

Can3452 Mar 21, 2013 9:42:13 AM
Are2904 Mar 21, 2013 9:37:08 AM
Pak2123 Mar 21, 2013 9:31:02 AM

Re: How to find the distinct value from the from two fields value .....

martin_mueller — Thu, 21 Mar 2013 13:02:11 GMT

To rephrase, you want to drop all rows where Field1's value exists somewhere in any Field2?

Re: How to find the distinct value from the from two fields value .....

kristian_kolb — Thu, 21 Mar 2013 13:13:18 GMT

Not fully understanding how your actual event look like, you could try something along the likes of;

Field1=* | search NOT [search Field2=* | rename Field2 as Field1 | fields + Field1]

The subsearch (within square brackets) will execute first and return its results to the outer search, like so;

Field1=*  | search NOT ((Field1=X) OR (Field1=Y) OR (Field1=Z))

Of course you might want to add constraints on sourcetypes, hosts etc etc.

You should always post a few sample events, since that will help the community to better understand your problem.

Re: How to find the distinct value from the from two fields value .....

dilstn — Thu, 21 Mar 2013 14:15:08 GMT

Here this table contains duplicates like same countryname repeated so , i need to find unique countryname with respect to Field1 . so i need the countryname with Field1 timings alone ......So eliminate the duplicate countryname ......by matching field1 with field2 ....

Re: How to find the distinct value from the from two fields value .....

dilstn — Thu, 21 Mar 2013 14:16:44 GMT

Here this table contains duplicates like same countryname repeated so , i need to find unique countryname with respect to Field1 . so i need the countryname with Field1 timings alone ...so eliminate the duplicate countryname...by matching field1 with field2....

Re: How to find the distinct value from the from two fields value .....

dilstn — Thu, 21 Mar 2013 14:23:11 GMT

Not unique countryname, i need countryname with respect to field1(timings) alone .....

Re: How to find the distinct value from the from two fields value .....

dilstn — Thu, 21 Mar 2013 14:23:24 GMT

Not unique countryname, i need countryname with respect to field1(timings) alone .....

Re: How to find the distinct value from the from two fields value .....

aholzer — Thu, 21 Mar 2013 19:41:33 GMT

Based on all your comments and responses to peoples comments it would seem that what you are looking for is the following:

Field1=* | dedup countryName sortby -Field1

This will ensure that you only pick up events that have a Field1 value (where Field1 is not null), and then remove duplicates on the countryName picking the one with the most recent Field1 value.