Compare field values between two different sourcetypes

[sriva6]

Hi,

I have two different sourcetypes and I am extrating two fields from the first sourcetype sourcetype1 and I need to check if the values of these fields are present in the second sourcetype sourcetype2.

I am using the below query to extract the fields from sourcetype1.

(sourcetype=sourcetype1) | rex "(?P<Field1>\d+\/\d+\/\d+\|\d+:\d+:\d+.\d+[^\|]+)" |  rex "\|Some String\|\w+\|(?P<Field2>[^\|]+)" 

Please note, Field1 is date and Field2 is a unique reference number.

Logs from Sourcetype1 look like this:

2013/12/10|20:36:00.427|INFO|Some String|user|U1234567|Success|End|866

Please tell me how can I search for the filed values in sourcetype2.

[kristian_kolb]

I think that there are a few things that you can do to improve your situation;

Firstly, you should consider extracting the fields from sourcetype1 differently. There is an excellent method for this when working with CSV or similarly structured files.

http://docs.splunk.com/Documentation/Splunk/6.0/Knowledge/Createandmaintainsearch-timefieldextractio...

Basically you configure a REPORT in props.conf and at corresponding stanza in transforms.conf. Remember that this is search time field extractions, so these configs should go on the Splunk instance where you do your searching (adjust fieldnames to suit your purposes);

props.conf

[sourcetype1]
REPORT-blah = pipe_sep

transforms.conf

[pipe_sep]
DELIMS = "|"
FIELDS = date, time, log_level, string, user, reference, status, sometext, number

What the other sourcetype looks like is unknown here, so we'll assume that it is the same field names. Then you can search like so for finding events that are present in sourcetype1 BUT NOT in sourcetype2;

sourcetype=sourcetype1 reference=* date=* time=* NOT [sourcetype=sourcetype2 reference=* date=* time=* | fields + reference, date, time] | table reference, date, time

Hope this helps you understand the structure of such a query using subsearches.

/k

[lguinn2]

If there are a small number of values for Field1 and Field2, this will work

sourcetype=sourcetype2 [ search sourcetype=sourcetype1 
| rex "(?P<Field1>\d+\/\d+\/\d+\|\d+:\d+:\d+.\d+[^\|]+)" 
|  rex "\|Some String\|\w+\|(?P<Field2>[^\|]+)" 
| dedup Field1 Field2 | format "(" "(" "OR" ")" "OR" ")" ]

If that doesn't work, I need to know more about the format of sourcetype2

[sriva6]

This didnt help, please see my question below:
http://answers.splunk.com/answers/115011/compare-dates-in-splunk

[kristian_kolb]

http://answers.splunk.com/answers/114818/find-the-difference-between-two-datetime-values?page=1&focu...

Adjust your strptime/strftime variables accordingly

[sriva6]

Also, is there a quick and easy way to compare dates/time in splunk if the date format is different.

for e.g Date1 = 2013-12-13T02:25:41.045
Date2 = 2013/12/13|02:30:52.274

[sriva6]

Hi, Thanks, could you tell me how can I check if the values of Field2 from sourcetype1 are not present in sourcetype2. I want to print all the values of Field2 from sourcetype1 which are not present in sourcetype2.
I have tried the below query but it didnt help:
sourcetype=sourcetype2 NOT [ search sourcetype=sourcetype1 | rex "(?P\d+\/\d+\/\d+|\d+:\d+:\d+.\d+[^|]+)" | rex "|Some String|\w+|(?P[^|]+)" | dedup Field1 Field2 | fields + Field2 | rename Field2 as query ]

[kristian_kolb]

oooh late again... 🙂