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
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.
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
| 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.
So, after correcting the criteria,