Reconciliation from 3 different sourcetypes

[ashrafsj]

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

[scelikok]

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

[scelikok]

Hi @ashrafsj,

You can use like below;

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

[ashrafsj]

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

[to4kawa]

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?

[ashrafsj]

@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)

[to4kawa]

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

This will be OK.

[ashrafsj]

@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)