topic How to get data from log and count event values in Splunk Search

How to get data from log and count event values

Rakesh915473 — Thu, 15 Jul 2021 06:10:57 GMT

Hello Team,

I'm very new to splunk, I have below two logs

"message": "api.main REQ user1 10.10.44.76 \"GET /api/v1/data?my_list=%25geo%25&our_list=%25school%25&query_string_list=%25college%25&page=1&per_page=100\"
"message": "api.main REQ user2 10.10.14.16 \"GET /api/v1/data?my_list=%25geo%25&our_list=%25office%25&query_string_list=%25school%25&page=1&per_page=100\"

I want to get data between "%25school%25" -> school.

So contains log1: school, collage

log2: office, school

We can count and show: school=2, office=1,collage=1, If possible we can plot in time chart 🙂

Thanks in advance.

Re: How to get data from log and count event values

splunkerer — Thu, 15 Jul 2021 06:38:12 GMT

give this a try.

| rex field=_raw "our_list=%25(?<new_field1>\w+)" | rex field=_raw "string_list=%25(?<new_field2>\w+)" | stats count by new_field1 new_field2 for timechart portion of you question: | timechart span=1d count by new_field1 new_field2 usenull=f

Re: How to get data from log and count event values

kamlesh_vaghela — Thu, 15 Jul 2021 06:26:34 GMT

@Rakesh915473

You can try this also,

YOUR_SEARCH | rex field=_raw "our_list=%25(?<our_list>.*)%25&query_string_list=%25(?<query_string_list>.*)%25" | table our_list query_string_list

Re: How to get data from log and count event values

venkatasri — Thu, 15 Jul 2021 06:28:47 GMT

Hi @Rakesh915473

Can you try this,

| rex "ur_list=%25(?<field1>\w+)%25&query_string_list=%25(?<field2>\w+)"

An upvote would be appreciated if this reply helps!

Re: How to get data from log and count event values

Rakesh915473 — Thu, 15 Jul 2021 07:33:11 GMT

Thankyou @splunkerer , It's working 🙂

Couple more questions please.

1) Could you please explain me in detail what this expression does, Please 🙂

(?<new_field>\W+)

2) If I want to exclude particular values fetch from new_field1 and new_field2. Ex: school and collage

Re: How to get data from log and count event values

splunkerer — Thu, 15 Jul 2021 16:46:30 GMT

Hey @Rakesh915473

I am glad it worked.

1. (?<new_field>\w+) ---> It was not capital "W", but 'w' This is Splunk regex syntax. There are 2 important portion of this

a. new_field : you can change this anything you want, at the end of the search you will see this name as a new field on the Splunk.

b. \w+ : this is regular expression to match your need. Basically \w+ this matches one or more word character.

All other characters are just standard format which should be there if you are looking for specific field extraction.

you can check these for more information

https://regex101.com/

https://docs.splunk.com/Documentation/Splunk/8.2.1/SearchReference/Rex

2. for exclusion you can do it on rex, but also you do it after field extraction for simplicity since you can easily exclude anything you want after extracting data .

for example:

after field extraction with rex you can use:

| where new_field1!=school OR new_field2!=school ....etc

in the rex:

| rex field=_raw "our_list=%25(?<new_field>(!school|collage)\w+)"
| rex field=_raw "string_list=%25(?<new_field>(!school|collage)\w+)"

Re: How to get data from log and count event values

Rakesh915473 — Fri, 16 Jul 2021 04:54:31 GMT

@splunkerer Great explanation 🙂 Very nice of you 🙂

Re: How to get data from log and count event values

Rakesh915473 — Fri, 16 Jul 2021 05:09:21 GMT

Hi @kamlesh_vaghela,

Why rex field=_raw ?? is it not supposed to be rex field=message as per my logs.

Please confirm why you mentioned _raw as field.

Re: How to get data from log and count event values

kamlesh_vaghela — Fri, 16 Jul 2021 05:24:02 GMT

@Rakesh915473

If your log extracting proper message field with proper then you can use it in rex else _raw will always be there.

Re: How to get data from log and count event values

splunkerer — Fri, 16 Jul 2021 06:50:14 GMT

you are welcome @Rakesh915473
if you dont mind, can you accept it as solution to close this thread.

Best,

Re: How to get data from log and count event values

Rakesh915473 — Mon, 19 Jul 2021 07:32:53 GMT

@splunkerer Accepted.

| rex field=_raw "our_list=%25(?<new_field1>(!school|collage)\w+)"
| rex field=_raw "string_list=%25(?<new_field2>(!school|collage)\w+)"

Unfortunately, School and collage is not excluding, moreover no data is showing, If I add

"| stats count by new_field new_field2"

How can we make it work?

Re: How to get data from log and count event values

Rakesh915473 — Fri, 23 Jul 2021 07:21:29 GMT

Hi @venkatasri

Can we skip one/two letter words, How can we do it from above search string?

Re: How to get data from log and count event values

venkatasri — Fri, 23 Jul 2021 10:48:58 GMT

@Rakesh915473

This one works for word with min 3 chars,

| rex "our_list=%25(?<field1>\w{3,})%25&query_string_list=%25(?<field2>\w{3,})"

An upvote would be appreciated if this reply helps!