How to use rex command to extract two fields and chart the count for both in one search query?

[Splunkster45]

Hello!
I've recently learned to create a field using the rex command and now I'm trying to modify it to create two fields. I'll give an example to show what I'm trying to do:

Suppose a log file contains logs of the form: "...Login failed for user..." and "... Login succeeded for user..." What I would like to do would be to get a count of each one and compare them to each other either in a table or using a bar chart.

The following query will give a count to the number of times succeeded is found. A similar thing can be done for 'failed' attempts, however how do I combine it into one string so that I can get data that I can look at side by side. My question is two fold:

1. How can I join queries so that I only have 1 query?
2. How can I compare them together/next to each other?

Unfortunately, I don't have access to the props folder to be able to create fields by default.

index=spss earliest=-25h | rex field=_raw ".*Login (?)" | chart count over succeeded

Thanks in advance!

[skender27]

Hi everyone,

I had almost the same question, but maybe easier.
I needed to extract only mydir from this path (which is a field itself called source)

/home/mydir/etc/etc2/....

I resolved it like this:

| rex field=source "^(\/home)\/(?\w+[^\/])"

Thanks anyway,
Skender

[sowings · Accepted solution]

If your events look like "Login failed for user bob" vs. "Login succeeded for user carol", then you can capture two fields at once within the same regex by something like this:

rex "Login (?<action><BACKSLASH>w+) for user (?<user><BACKSLASH>w+)"

This would capture both "action" as "succeeded" or "failed" and the "username" field with the value of the user's login name.

You could then, say "timechart count by action", differentiating by the value of the action field. Alternately, "timechart count by user" would show attempts (whether successful or not) by each user.
Finally, you could also do chart count OVER user BY action. Try it out.

[sowings]

For some reason I can't make a backslash appear in my string above, even if I escape a backslash to escape another backslash (like four of them in a row).

[Splunkster45]

...cont
I do not get an action field at all!
To make sure that I have valid logs to search, I use the following search:

index=spss earliest=-25h “Login” | rex field=_raw "Login (?<action>w+) for user: "

This search gives 48 results each of the form: ‘TIMESTAMP [NUMBER] Login succeeded/failed for user: USER’. I can see that I do have valid logs. I should be picking up values for action, but the action field is not listed in the right hand sidebar (even if I select ‘view all’)
However, if I insert the below code

index=spss earliest=-25h  "Login" | rex field=_raw ".*Login succeeded for user: (?<user>.*)"

The field user is listed on the right hand sidebar and has 4 values. It appears to be working as it should. If I remove the second ‘.*’ from the code, the user field only has 1 value which is blank. The last two codes are very similar, but the differences are causing the former to break and the latter to work.

Sorry for the information overload. Does anyone have any advice? Thanks in advance!

[sowings]

The forum doesn't seem to be correctly displaying the backslash character, but you'll need a backslash in front of your w+ in the regular expression to capture "one or more word characters". The literal . in your user regex captures any character, including whitespace, so that's why it actually found user data.

[Splunkster45]

Thanks! I didn't know that about the backslash command. All makes sense now. I understand!

This works:

index=spss earliest=-25h@h Login | rex field=_raw "Login (?<action>\w+) for user: (?<username>\w+)"

Y'all have a great weekend!

[sowings]

You'll want to look at a regular expression tool to validate your capture groups. I like regexr; it has both a web form mode as well as a standalone app I can use on my mac. I suspect that simply the capture group is not matching the event string correctly.

[chris]

This might work for you:

  index=spss earliest=-25h| rex "Login (?<action>w+) for user (?<username>w+)" | stats count(eval(action="failed")) as fail_count, count(eval(action="succeeded")) as succeeded_count by user | eval ratio=fail_count/succeeded_count

[Splunkster45]

Thanks for the replies! I feel that I have a better understanding at what is going on. When I used Chris’s code and got a ‘No results found. Inspect’ error message. I think that may just be a syntax error and so I simplified the code. When I did so gained a better idea of what the issue is. In the following example I am going to stick with trying to create a field call action with two options: succeeded and failed. I will not worry about the user field.

When I insert the below code

index=spss earliest=-25h | rex "Login (?<action>)" | stats count(eval(action=="succeeded")) as succeeded_count   count(eval(action=="failed")) as fail_count 

I get a 0 for both succeeded_count and fail_count. In looking at the left hand sidebar, the action field has 1 value and that value is blank.

If I modify the rex command slightly

index=spss earliest=-25h | rex "Login (?<action>) for user" | stats count(eval(action=="succeeded")) as succeeded_count  count(eval(action=="failed")) as fail_count

cont...