topic Re: Reconciliation from 3 different sourcetypes in Splunk Search

Reconciliation from 3 different sourcetypes

ashrafsj — Mon, 08 Feb 2021 10:38:28 GMT

Hi All,

I have an issue while trying to reconcile events from 3 different source types, the events from each sourcetype below is from DBConnect, they run every 1 hour. I tried to schedule the alert to look into events last 3 hours, i get all the events instead of just the difference. tried mvexpand as well but didnt help much. the whole idea is to reconcile events from 3 different sourcetypes and get the result od missing SMS_RECORD out to team to look into.

index=month source=XYZ sourcetype=OVERDRAFT_REC1
| dedup SMS_RECORD
| eval TIMESTAMP=strftime(strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S"),"%d-%m-%Y %H:%M")
| stats values(SMS_RECORD) AS IN_T1 BY TIMESTAMP
| append
[ search index=month source=XYZ sourcetype=OVERDRAFT_REC2
| dedup SMS_RECORD
| eval TIMESTAMP=strftime(strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q"),"%d-%m-%Y %H:%M")
| stats values(SMS_RECORD) AS IN_I1 BY TIMESTAMP
]
| append
[ search index=month source=XYZ sourcetype=OVERDRAFT_REC3
| dedup SMS_RECORD
| eval TIMESTAMP=strftime(strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q"),"%d-%m-%Y %H:%M")
| stats values(SMS_RECORD) AS IN_TXT1 BY TIMESTAMP
]
| mvexpand IN_T1
| mvexpand IN_I1
| mvexpand IN_TXT1
| stats values(IN_T1) AS T1,values(IN_I1) AS I1,values(IN_TXT1) AS TXT1 BY TIMESTAMP
| where T1!=I1 OR I1!=TXT1 OR TXT1!=T1

Re: Reconciliation from 3 different sourcetypes

to4kawa — Mon, 08 Feb 2021 11:07:20 GMT

index=month source=XYZ (sourcetype=OVERDRAFT_REC1 OR sourcetype=OVERDRAFT_REC2 OR sourcetype=OVERDRAFT_REC3) | eval TIMESTAMP=strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S") | bin span=1min TIMESTAMP | stats values(SMS_RECORD) AS SMS_RECORD BY TIMESTAMP sourcetype | mvexpand SMS_RECORD | where your_condition

Why not this?

Re: Reconciliation from 3 different sourcetypes

ashrafsj — Mon, 08 Feb 2021 12:36:40 GMT

@to4kawa - the timestamp of sourcetype1 and 2&3 are different.

sourcetype1 - %d-%b-%y %H:%M:%S

sourcetype2&3 - %Y-%m-%d %H:%M:%S.%6Q

once I convert them to same timestamp - %d-%m-%Y %H:%M , it would be easier to reconcile the events. if the entries from each of the sourcetypes are not equal, then it needs to display the SMS_RECORD (difference)

Re: Reconciliation from 3 different sourcetypes

to4kawa — Mon, 08 Feb 2021 12:56:30 GMT

| eval TIMESTAMP=coalesce(strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S"),strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q"))

This will be OK.

Re: Reconciliation from 3 different sourcetypes

ashrafsj — Mon, 08 Feb 2021 16:55:20 GMT

@to4kawa is there a way i can implement a condition which says the SMS_RECORD is not available in any one of the sourcetype.

Pardon me for the syntax, but some thing like this

| where NOT SMS_RECORD IN (sourcetype=OVERDRAFT_REC1 OR sourcetype=OVERDRAFT_REC2 OR sourcetype=OVERDRAFT_REC3)

Re: Reconciliation from 3 different sourcetypes

scelikok — Mon, 08 Feb 2021 18:09:04 GMT

Hi @ashrafsj,

You can use like below;

| where isnull(SMS_RECORD) AND (sourcetype="OVERDRAFT_REC1" OR sourcetype="OVERDRAFT_REC2" OR sourcetype="OVERDRAFT_REC3")

Re: Reconciliation from 3 different sourcetypes

ashrafsj — Mon, 08 Feb 2021 18:42:52 GMT

Hi @scelikok ,

Im trying to add the condition to compare the SMS_RECORD output among 3 different source types and only output SMS_RECORD entry that didnt reconcile between the 3 sourcetypes

index=month source=XYZ (sourcetype=OVERDRAFT_REC1 OR sourcetype=OVERDRAFT_REC2 OR sourcetype=OVERDRAFT_REC3) | eval TIMESTAMP=coalesce(strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S"),strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q")) | bin span=1min TIMESTAMP | stats values(SMS_RECORD) AS SMS_RECORD BY TIMESTAMP sourcetype | mvexpand SMS_RECORD | where <<check SMS_RECORD that doesnt reconcile from the 3 different sourcetypes>>

Re: Reconciliation from 3 different sourcetypes

scelikok — Mon, 08 Feb 2021 21:39:25 GMT

Hi @ashrafsj,

My reply was only for your last question, I think now I understood your need. Please try below;

index=month source=XYZ (sourcetype=OVERDRAFT_REC1 OR sourcetype=OVERDRAFT_REC2 OR sourcetype=OVERDRAFT_REC3) | eval TIMESTAMP=coalesce(strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S"),strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q")) | bin span=1min TIMESTAMP | stats dc(sourcetype) AS sourcetype_count values(sourcetype) as sourcetype BY TIMESTAMP SMS_RECORD | where sourcetype_count<3