Extract from Multiple Fields and Consolidate using Stats Count

[sridharadurthi]

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?

[yuanliu]

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.

[sridharadurthi]

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

[yuanliu]

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

[sridharadurthi]

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

[yuanliu]

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

[sridharadurthi]

@yuanliu 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

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

[ITWhisperer]

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

[sridharadurthi]

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

[ITWhisperer]

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'

[sridharadurthi]

No luck 😞 

[ITWhisperer]

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!

[sridharadurthi]

@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'

[ITWhisperer · Accepted solution]

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"

[sridharadurthi]

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

[ITWhisperer]

Please share the search which is giving this result.

[sridharadurthi]

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