topic Re: Extract from Multiple Fields and Consolidate using Stats Count in Splunk Search

Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Mon, 08 Jul 2024 23:05:46 GMT

Hi Team,

I have two different fields (Ex. A and B). Value A will come for some results and B will come for some. While I am using below query, it is only pulling A or B.

index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | stats count by A StatusCode - only A events are getting displayed index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | stats count by B StatusCode - only B events are getting displayed index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | stats count by A B StatusCode - it is not displaying any table

How to display both A and B colums combined and have the status code as well in the table?

Re: Extract from Multiple Fields and Consolidate using Stats Count

yuanliu — Mon, 08 Jul 2024 21:00:12 GMT

You need to first think through what the problem you are trying to solve by using sample data. Let's say the search index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 returns the following results.

StatusCode	A	B
200	some A value
200	some other A value
200		Some B value
200		Some other B value
200	Even more A value

Can you show the result table that you are looking for?

How to display both A and B colums combined and have the status code as well in the table?

If A and B do not exist in the same event, I see no meaningful way to display both of them.

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Mon, 08 Jul 2024 21:09:29 GMT

Thanks for swift reply @yuanliu . Here is the sample table I am looking for

StatusCode	A or B	Count
200	some A/B value	2
200	some A/B value	1
200	some A/B value	10
200	some A/B value	8
200	some A/B value	5

A & B coulmns should come together as one and based on their values it should add to the count. More detailed way

StatusCode	A or B	Count
200	Upgrade	2
200	Downgrade	1
200	Retain	10
200	Cancel	8
200	New Customer	5

Re: Extract from Multiple Fields and Consolidate using Stats Count

yuanliu — Mon, 08 Jul 2024 23:11:08 GMT

A & B coulmns should come together as one and based on their values it should add to the count.

This is clearer than the original description. If A and B should come together, they have to be combined before groupby, and used as a single groupby. The reason why your first attempt did produce results is because as two separate groupby terms, both must exist in the same events.

The solution will depend on whether A and B are mutually exclusive. From your original result, it seems that they are exclusive. So,

index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | eval "A OR B" = coalesce(A, B) | stats count by "A OR B" StatusCode

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Mon, 08 Jul 2024 21:31:32 GMT

When I am trying your query, it is showing that number events on the top. But not displayin the results in the statistics. Like below

Re: Extract from Multiple Fields and Consolidate using Stats Count

ITWhisperer — Mon, 08 Jul 2024 22:19:40 GMT

Please share the search which is giving this result.

Re: Extract from Multiple Fields and Consolidate using Stats Count

yuanliu — Mon, 08 Jul 2024 23:10:19 GMT

This only means that your data set is not as you described. Alternatively, there was some mistake in your search as @ITWhisperer speculated. For example, maybe you misspelled A or B (these are not real field names I am certain).

I can run my code with this mock dataset:

A	B	StatusCode
Upgrade		200
	Downgrade	200
	Upgrade	200
Retain		200
	Cancel	200
Cancel		200
	Cancel	200
Cancel		200
	Cancel	200
Cancel		200
	Cancel	200
Cancel		200
	Retain	200
Retain		200
Retain		200
	Retain	200
Retain		200
Retain		200
	Retain	200
Retain		200

If you manually count, this dataset should give your mock result and it does. Here is full emulation that you can run an compare with real data:

| makeresults format=csv data="StatusCode, A, B 200, Upgrade, 200, , Downgrade 200, , Upgrade 200, Retain, 200, , Cancel 200,Cancel, 200, , Cancel 200,Cancel, 200, , Cancel 200,Cancel, 200, , Cancel 200,Cancel, 200, , Retain 200, Retain, 200, Retain, 200, , Retain 200, Retain, 200, Retain, 200, , Retain 200, Retain, 200, Retain, 200, New Customer, 200, , New Customer 200, , New Customer 200, New Customer, 200, , New Customer" ``` the above emulates index="XYZ" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 ``` | eval "A OR B" = coalesce(A, B) | stats count by "A OR B" StatusCode

The output is exactly like your mock result:

A OR B	StatusCode	count
Cancel	200	8
Downgrade	200	1
New Customer	200	5
Retain	200	10
Upgrade	200	2

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 13:40:49 GMT

this is the query which I am using to filter the data

index="apigee" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | eval "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" = coalesce(BackendResponse.content.reasonCode, ConsumerResponse.content.reasonCode) | stats count by "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" StatusCode

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 14:21:30 GMT

@yuanliu this is the query which I am using to filter the data

It is showing the event count, but it is not generating the results. Highlited the same.

Re: Extract from Multiple Fields and Consolidate using Stats Count

ITWhisperer — Tue, 09 Jul 2024 15:23:13 GMT

When field names have special characters in, they often need single quotes around them (double if they are on the left of the assignment). Try this

| eval "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" = coalesce('BackendResponse.content.reasonCode', 'ConsumerResponse.content.reasonCode') | stats count by 'BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode' StatusCode

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 15:37:26 GMT

@ITWhisperer , I tried but no luck 😞 . It is displaying the count but not displaying the stats

Re: Extract from Multiple Fields and Consolidate using Stats Count

ITWhisperer — Tue, 09 Jul 2024 16:22:35 GMT

Try without StatusCode on the stats

index="apigee" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | eval "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" = coalesce('BackendResponse.content.reasonCode', 'ConsumerResponse.content.reasonCode') | stats count by 'BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode'

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 16:26:30 GMT

No luck 😞

Re: Extract from Multiple Fields and Consolidate using Stats Count

ITWhisperer — Tue, 09 Jul 2024 16:34:44 GMT

Please try it exactly as I showed you - I have already explained that you need double quotes to the left of the assignment and single quotes to the right - if you do not follow simple instructions like this, you will struggle to get a working solution!

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 16:42:38 GMT

@ITWhispererthanks for being patient with me. I have copied the same query which you have mentioned and this is the result

index="apigee" sourcetype!="apigee:nginx" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | eval "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" = coalesce('BackendResponse.content.reasonCode', 'ConsumerResponse.content.reasonCode') | stats count by 'BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode'

Re: Extract from Multiple Fields and Consolidate using Stats Count

ITWhisperer — Tue, 09 Jul 2024 16:50:56 GMT

OK try it with double quotes on the stats command (which is counter-intuitive!)

index="apigee" (ProxyPath="/xyz" OR ProxyPath="/abc") AND StatusCode=200 | eval "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode" = coalesce('BackendResponse.content.reasonCode', 'ConsumerResponse.content.reasonCode') | stats count by "BackendResponse.content.reasonCode OR ConsumerResponse.content.reasonCode"

Re: Extract from Multiple Fields and Consolidate using Stats Count

sridharadurthi — Tue, 09 Jul 2024 16:56:20 GMT

Finally it is working now 🙂 Thank you so much. You made my day.