Splunk Search

Reconciliation from 3 different sourcetypes

ashrafsj
Path Finder

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

 

Labels (3)
Tags (1)
0 Karma

scelikok
Champion

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
If this reply helps you an upvote is appreciated.
0 Karma

scelikok
Champion

Hi @ashrafsj,

You can use like below;

| where isnull(SMS_RECORD) AND (sourcetype="OVERDRAFT_REC1" OR sourcetype="OVERDRAFT_REC2" OR sourcetype="OVERDRAFT_REC3")
If this reply helps you an upvote is appreciated.
0 Karma

ashrafsj
Path Finder

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

 

0 Karma

to4kawa
SplunkTrust
SplunkTrust
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?

0 Karma

ashrafsj
Path Finder

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

0 Karma

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

This will be OK.

0 Karma

ashrafsj
Path Finder

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

 

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.