Need help to combine queries

[mahesh27]

Query1:

index=app-index source=application.logs "Initial message received with below details"
|rex field= _raw "RampData :\s(?<RampdataSet>\w+)"
|stats count as IntialMessage by RampdataSet

output:

RampdataSet	IntialMessage
WAC	10
WAX	30
WAM	22
STC	33
STX	66
OTP	20

Query2:

index=app-index source=application.logs "Initial message Successfull"
|rex field= _raw "RampData :\s(?<RampdataSet>\w+)"
|stats count as SuccessfullMessage by RampdataSet

output:

RampdataSet	SuccessfullMessage
WAC	0
WAX	15
WAM	20
STC	12
STX	30
OTP	10
TTC	5
TAN	7
TXN	10
WOU	12

Query3:

index=app-index source=application.logs "Initial message Error"
|rex field= _raw "RampData :\s(?<RampdataSet>\w+)"
|stats count as ErrorMessage by RampdataSet

output:

RampdataSet	ErrorMessage
WAC	0
WAX	15
WAM	20
STC	12

We want to combine three queries and want to get the output as shown below, how to do that???

RampdataSet	IntialMessage	SuccessfullMessage	ErrorMessage	Total
WAC	10	0	0	10
WAX	30	15	15	60
WAM	22	20	20	62
STC	33	12	12	57
STX	66	30	0	96
OTP	20	10	0	30
TTC	0	5	0	5
TAN	0	7	0	7
TXN	0	10	0	10
WOU	0	12	0	12

[bowesmana]

Try

index=app-index source=application.logs ("Initial message received with below details" OR "Initial message Successfull" OR "Initial message Error")
| rex field= _raw "RampData :\s(?<RampdataSet>\w+)"
| rex "Initial message (?<type>\w+)"
| chart count over RampdataSet by type
| addtotals

This extracts a 'type' field which will be received, Error or Successfull and then the chart command will do what you want - it will give you fields names as above, but you can rename those to what you want.

[mahesh27]

Hi @bowesmana , Thank you for sharing the query, it worked.
But i have another query, how do we write rex to extract  these strings:

index=app-index source=application.logs ("Initial message received with below details" OR "Letter published correctley to ATM subject" OR Letter published correctley to DMM subject" OR "Letter rejected due to: DOUBLE_KEY" OR "Letter rejected due to: UNVALID_LOG" OR "Letter rejected due to: UNVALID_DATA_APP")

[bowesmana · Accepted solution]

What do you want to extract?

See this example which extracts parts  of the text 

| makeresults
| fields - _time
| eval msgs=split("Initial message received with below details,Letter published correctley to ATM subject,Letter published correctley to DMM subject,Letter rejected due to: DOUBLE_KEY,Letter rejected due to: UNVALID_LOG,Letter rejected due to: UNVALID_DATA_APP",",")
| mvexpand msgs
| rex field=msgs "(Initial message |Letter published correctley to |Letter rejected due to: )(?<reason>.*)"

you'll need to decide what you want and what you intend to use it for.

[mahesh27]

Hi @bowesmana, As you suggested We tried below query, but i am getting same values for each msgs strings. Can you please let me know is my query correct??

index=app-index source=application.logs 
|rex field= _raw "RampData :\s(?<RampdataSet>\w+)"
| eval msgs=split("Initial message received with below details,Letter published correctley to ATM subject,Letter published correctley to DMM subject,Letter rejected due to: DOUBLE_KEY,Letter rejected due to: UNVALID_LOG,Letter rejected due to: UNVALID_DATA_APP",",")
| mvexpand msgs
| rex field=msgs "(Initial message |Letter published correctley to |Letter rejected due to: )(?<reason>.*)"
|chart count over RampdataSet by reason
|addtotals

OUTPUT:

Rails	below details	ATM subject	DMM subject	DOUBLE_KEY	UNVALID_LOG	UNVALID_DATA_APP	Total
WAC	0	0	0	0	0	0	0
WAX	15	15	15	15	15	15	90
WAM	20	20	20	20	20	20	120
STC	12	12	12	12	12	12	72
STX	30	30	30	30	30	30	180
OTP	10	10	10	10	10	10	60
TTC	5	5	5	5	5	5	30
TAN	7	7	7	7	7	7	42
TXN	10	10	10	10	10	10	60
WOU	12	12	12	12	12	12	72

[bowesmana]

My code was an example using your data - you are using that fixed set of strings in your code - you should do the rex against your raw data not the fixed msgs field - remove the eval msgs.... and the mvexpand, that was just example code.

Your rex statement should either use _raw or if you have those messages extracted to a separate field, use that field.

[mahesh27]

@bowesmana , thank you for ur inputs.

We created queries according to our data working now.

Thank you once again.

[mahesh27]

@bowesmana , Thank you so much, it worked😊