Dashboards & Visualizations
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
Ask a Question
Solved! Jump to solution

Extract fields from transaction

nadeige1
Engager
‎02-04-2021 07:37 AM

Hello,

 

I have logs with this format and I want to count the number of error code by SNR.

 

2021-01-25 12:59:18,355 - [INFO] SNR: 917173
2021-01-25 12:59:21,868 - [INFO] 0x100:S_Home
2021-01-25 12:59:22,312 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:22,314 - [INFO] 0x154:S_VACON
2021-01-25 12:59:22,316 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:22,629 - [INFO] new file cycle: 17320
2021-01-25 12:59:23,141 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,142 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:23,741 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,742 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,645 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,646 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:25,721 - [INFO] 0x100:S_Home
2021-01-25 12:59:27,095 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:27,102 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,104 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:27,425 - [INFO] new file cycle: 17321
2021-01-25 12:59:27,952 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,953 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:28,856 - [INFO] 0x154:S_VACON
2021-01-25 12:59:28,857 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,450 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,451 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:30,504 - [INFO] 0x100:S_Home
2021-01-25 12:59:31,624 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:31,625 - [INFO] 0x154:S_VACON
2021-01-25 12:59:31,627 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:31,951 - [INFO] new file cycle: 17322
2021-01-25 12:59:32,478 - [INFO] 0x154:S_VACON
2021-01-25 12:59:32,479 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:33,432 - [INFO] 0x154:S_VACON
2021-01-25 12:59:33,433 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,920 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,922 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:34,993 - [INFO] 0x100:S_Home
2021-01-25 12:59:36,321 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:36,325 - [INFO] 0x154:S_VACON
2021-01-25 12:59:36,327 - [INFO] 0x152:S_MQLOFF

2021-01-25 12:59:18,355 - [INFO] ADU identified SNR: 917175
2021-01-25 12:59:37,190 - [INFO] 0x154:S_VACON
2021-01-25 12:59:37,190 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:38,157 - [INFO] 0x154:S_VACON
2021-01-25 12:59:38,158 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,674 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,676 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:39,742 - [INFO] 0x100:S_Home
2021-01-25 12:59:40,902 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:40,904 - [INFO] 0x154:S_VACON
2021-01-25 12:59:40,906 - [INFO] 0x152:S_MQLOFF

In this case, the result I expect is the following result:

Error code 0x130: 4 occurrences for SNR 917173, 1 occurrence for SNR: 917175

Error code 0x154: 7 occurrences for SNR 917173, 3 occurrences for SNR: 917175

etc.

I use the transaction command in order to group the events:

transaction startswith="SNR"

But when doing this, the number of error code is reduced to the number of event, which is wrong for me. Do you have an idea how to reach the result I expect

Labels (1)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

jacobpevans
Motivator
‎02-05-2021 01:53 AM

Greetings @nadeige1 ,

I have a few points to make.

First, try not to use transaction when not necessary. Splunk explicitly says this; the reason is that it is not as efficient as other commands that can often do the same thing, see here.

Second, I need to make two assumptions.

  1. The line 2021-01-25 12:59:18,355 - [INFO] ADU identified SNR: 917175 is a copy/paste from the first SNR line, and the timestamp should actually be more like 2021-01-25 12:59:36,777 so that your sample data is in _time sorted order.
  2. Error code 0x154: 7 occurrences for SNR 917173 is a miscalculation. It should really be 10.

If those assumptions are correct, here is a run-anywhere search that will get you what you're asking:

| makeresults
| eval _raw =
"2021-01-25 12:59:18,355 - [INFO] SNR: 917173
2021-01-25 12:59:21,868 - [INFO] 0x100:S_Home
2021-01-25 12:59:22,312 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:22,314 - [INFO] 0x154:S_VACON
2021-01-25 12:59:22,316 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:22,629 - [INFO] new file cycle: 17320
2021-01-25 12:59:23,141 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,142 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:23,741 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,742 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,645 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,646 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:25,721 - [INFO] 0x100:S_Home
2021-01-25 12:59:27,095 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:27,102 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,104 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:27,425 - [INFO] new file cycle: 17321
2021-01-25 12:59:27,952 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,953 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:28,856 - [INFO] 0x154:S_VACON
2021-01-25 12:59:28,857 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,450 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,451 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:30,504 - [INFO] 0x100:S_Home
2021-01-25 12:59:31,624 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:31,625 - [INFO] 0x154:S_VACON
2021-01-25 12:59:31,627 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:31,951 - [INFO] new file cycle: 17322
2021-01-25 12:59:32,478 - [INFO] 0x154:S_VACON
2021-01-25 12:59:32,479 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:33,432 - [INFO] 0x154:S_VACON
2021-01-25 12:59:33,433 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,920 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,922 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:34,993 - [INFO] 0x100:S_Home
2021-01-25 12:59:36,321 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:36,325 - [INFO] 0x154:S_VACON
2021-01-25 12:59:36,327 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:36,777 - [INFO] ADU identified SNR: 917175
2021-01-25 12:59:37,190 - [INFO] 0x154:S_VACON
2021-01-25 12:59:37,190 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:38,157 - [INFO] 0x154:S_VACON
2021-01-25 12:59:38,158 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,674 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,676 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:39,742 - [INFO] 0x100:S_Home
2021-01-25 12:59:40,902 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:40,904 - [INFO] 0x154:S_VACON
2021-01-25 12:59:40,906 - [INFO] 0x152:S_MQLOFF"
| rex max_match=0 "^(?<raw>.+)\n+"
| fields - _time _raw
| mvexpand raw
| rename raw as _raw
| eval _time = strptime(substr(_raw, 0, 23), "%Y-%m-%d %H:%M:%S,%N")

| sort 0 _time
| rex "SNR:\s+(?<snr_id>\d+)"
| rex "(?<error_code>0x\d+)"
| streamstats last(snr_id) as snr_id
| stats count by snr_id error_code
| sort 0 error_code snr_id

 

A few notes:

  • Everything above the double new line is just for the "run-anywhere" part of the search. You will only need the part of the search below the double new line (starting with | sort 0 _time)
  • I had to extract snr_id and error_code. Hopefully, you already have these fields extracted in your data and should use your field names instead.

This is what my output looks like:

snr_id	error_code	count
917173	0x100	4
917175	0x100	1
917173	0x130	4
917175	0x130	1
917173	0x151	3
917175	0x151	1
917173	0x152	10
917175	0x152	2
917173	0x154	10
917175	0x154	3
917173	0x156	3
917175	0x156	1
Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.

View solution in original post

0 Karma
Reply
Solved! Jump to solution
Solution

jacobpevans
Motivator
‎02-05-2021 01:53 AM

Greetings @nadeige1 ,

I have a few points to make.

First, try not to use transaction when not necessary. Splunk explicitly says this; the reason is that it is not as efficient as other commands that can often do the same thing, see here.

Second, I need to make two assumptions.

  1. The line 2021-01-25 12:59:18,355 - [INFO] ADU identified SNR: 917175 is a copy/paste from the first SNR line, and the timestamp should actually be more like 2021-01-25 12:59:36,777 so that your sample data is in _time sorted order.
  2. Error code 0x154: 7 occurrences for SNR 917173 is a miscalculation. It should really be 10.

If those assumptions are correct, here is a run-anywhere search that will get you what you're asking:

| makeresults
| eval _raw =
"2021-01-25 12:59:18,355 - [INFO] SNR: 917173
2021-01-25 12:59:21,868 - [INFO] 0x100:S_Home
2021-01-25 12:59:22,312 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:22,314 - [INFO] 0x154:S_VACON
2021-01-25 12:59:22,316 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:22,629 - [INFO] new file cycle: 17320
2021-01-25 12:59:23,141 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,142 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:23,741 - [INFO] 0x154:S_VACON
2021-01-25 12:59:23,742 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,645 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:25,646 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:25,721 - [INFO] 0x100:S_Home
2021-01-25 12:59:27,095 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:27,102 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,104 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:27,425 - [INFO] new file cycle: 17321
2021-01-25 12:59:27,952 - [INFO] 0x154:S_VACON
2021-01-25 12:59:27,953 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:28,856 - [INFO] 0x154:S_VACON
2021-01-25 12:59:28,857 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,450 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:30,451 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:30,504 - [INFO] 0x100:S_Home
2021-01-25 12:59:31,624 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:31,625 - [INFO] 0x154:S_VACON
2021-01-25 12:59:31,627 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:31,951 - [INFO] new file cycle: 17322
2021-01-25 12:59:32,478 - [INFO] 0x154:S_VACON
2021-01-25 12:59:32,479 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:33,432 - [INFO] 0x154:S_VACON
2021-01-25 12:59:33,433 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,920 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:34,922 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:34,993 - [INFO] 0x100:S_Home
2021-01-25 12:59:36,321 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:36,325 - [INFO] 0x154:S_VACON
2021-01-25 12:59:36,327 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:36,777 - [INFO] ADU identified SNR: 917175
2021-01-25 12:59:37,190 - [INFO] 0x154:S_VACON
2021-01-25 12:59:37,190 - [INFO] 0x151:S_MQLON
2021-01-25 12:59:38,157 - [INFO] 0x154:S_VACON
2021-01-25 12:59:38,158 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,674 - [INFO] 0x152:S_MQLOFF
2021-01-25 12:59:39,676 - [INFO] 0x156:S_VACOFF
2021-01-25 12:59:39,742 - [INFO] 0x100:S_Home
2021-01-25 12:59:40,902 - [INFO] 0x130:S_Cycle
2021-01-25 12:59:40,904 - [INFO] 0x154:S_VACON
2021-01-25 12:59:40,906 - [INFO] 0x152:S_MQLOFF"
| rex max_match=0 "^(?<raw>.+)\n+"
| fields - _time _raw
| mvexpand raw
| rename raw as _raw
| eval _time = strptime(substr(_raw, 0, 23), "%Y-%m-%d %H:%M:%S,%N")

| sort 0 _time
| rex "SNR:\s+(?<snr_id>\d+)"
| rex "(?<error_code>0x\d+)"
| streamstats last(snr_id) as snr_id
| stats count by snr_id error_code
| sort 0 error_code snr_id

 

A few notes:

  • Everything above the double new line is just for the "run-anywhere" part of the search. You will only need the part of the search below the double new line (starting with | sort 0 _time)
  • I had to extract snr_id and error_code. Hopefully, you already have these fields extracted in your data and should use your field names instead.

This is what my output looks like:

snr_id	error_code	count
917173	0x100	4
917175	0x100	1
917173	0x130	4
917175	0x130	1
917173	0x151	3
917175	0x151	1
917173	0x152	10
917175	0x152	2
917173	0x154	10
917175	0x154	3
917173	0x156	3
917175	0x156	1
Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma
Reply
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Mastering Threat Intelligence in ES 8.5, Splunk AI Assistant v2, and More from Splunk ...

Splunk Lantern is Splunk’s customer success center that provides practical guidance from Splunk experts on key ...

Break the Build: Inside the KubeDoom Lounge at .conf26

    You step up to the machine. The pixelated corridors of a certain 1993 FPS load in front of you, EMP Pulse ...

Splunk Auto Ingestion Parallel Pipeline Scaling

Why this feature matters Many Splunk environments experience ingestion pressure long before the host is fully ...