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
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
Hi @ashrafsj,
You can use like below;
| where isnull(SMS_RECORD) AND (sourcetype="OVERDRAFT_REC1" OR sourcetype="OVERDRAFT_REC2" OR sourcetype="OVERDRAFT_REC3")
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>>
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?
@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)
| eval TIMESTAMP=coalesce(strptime(TIMESTAMP,"%d-%b-%y %H:%M:%S"),strptime(TIMESTAMP,"%Y-%m-%d %H:%M:%S.%6Q"))
This will be OK.
@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)