How to extract field from one event and search field one by one in other event

[cwer]

This event is printed eveytime UserPin AreaCode AreaNum Sector Short Sem are unique for each userid and come only inside User Login successfully message with timestamp

"message":" *** User Login successfully credentials userid 2NANO-323254-7654-4  UserPin - 287654 AreaCode - 98765 AreaNum - 98765 Sector - 87612345 Short Sem - ZEB"

Below these two event are only printed when certain conditions are meet. I am very new in Splunk like a naive, how can we write a Splunk query such that take out the userid with UserPin AreaCode AreaNum Sector Short Sem which have the below printed event then only create a table with userid. If below two message are not printed with userid from above message then we should not consider the userid

"message": "User Failed to login userid - 2NANO-323254-7654-4"

"message": "User is from stackoverflow group, on XZ ABCE for userid - 2NAN0-323254-7654-4"

this is table structure where i want to fill values

UserId | UserPin | AreaCode | AreaNum | Sector | Short_Sem

I am very new in splunk can someone guide how to start to build where to look for the thing. Any hint or demo will work. Thank you

Example

"message":" *** User Login successfully credentials userid 2NANO-323254-7654-4  UserPin - 287654 AreaCode - 98765 AreaNum - 98765 Sector - 87612345 Short Sem - ZEB"

"message": "User Failed to login userid - 2NANO-323254-7654-4"

"message": "User is from stackoverflow group, on XZ ABCE for userid - 2NAN0-323254-7654-4"

"message":" *** User Login successfully credentials userid 2ABDO-54312-7654-4  UserPin - 287654 AreaCode - 98765 AreaNum - 98765 Sector - 87612345 Short Sem - ZEB"

"message":" *** User Login successfully credentials userid 2COMA-765234-8653-4  UserPin - 287654 AreaCode - 98765 AreaNum - 98765 Sector - 87612345 Short Sem - ZEB"

So we consider first only because that userid have has two more event with same userid and associated all the event have timestamp

UserId              | UserPin| AreaCode | AreaNum | Sector   | Short_Sem
2NANO-323254-7654-4 | 287654 | 98765    | 98765   | 87612345 | ZEB

[yuanliu]

Try this:

| rex "userid (- ){0,1}(?<userid>[^\s\"]+)" ``` extract common field userid ```
| rex "UserPin - (?<UserPin>\w+) AreaCode - (?<AreaCode>\w+) AreaNum - (?<AreaNum>\w+) Sector - (?<Sector>\w+) Short Sem - (?<Short_Sem>\w+)" ``` only in successful logins ```
| rex "User (?<of_interest>Failed to login|is from stackoverflow group)" ``` message of interest ```
| stats values(*) as * by userid ``` group by common userid ```
| where mvcount(of_interest)=2 ``` criteria ```
| fields - of_interest

In the above, the interesting level is set at count of two messages of interest.  You can rephrase it to what is of real importance.  For example, you can say "| where isnotnull(of_interest)" if the mere existence of any such message is of importance.

[cwer]

where mvcount(of_interest)=2  can you please explain what does it do and how will this help in creating table structure because it does not have table command 

fields - of_interest what does it will do 

[yuanliu]

• mvcount(of_interest) counts how many values eventstats has turned up for userid.  So, this checks for every userid that has 2 such events.  In fact, I forgot to check for successful login as well, i.e., that where filter  should read like

| where isnotnull(UserPin) AND mvcount(of_interest)=2 ``` criteria ```

(Here, only one field that is unique to successful login needs to be tested; UserPin is just a convenient choice.)  Any userid that do not meet these two criteria will be removed.

• fields - of_interest removes of_interest from listing.  You do not have to remove it if there are other things to do and don't mind streaming one more field.

So, after correcting the criteria,