topic Re: How to add multiple fields to chart count over in Splunk Search

How to add multiple fields to chart count over

kuul13 — Fri, 10 May 2024 16:36:40 GMT

This was my original query to get the list of apis that failed for a client. I have more details of the client in the lookup table. How can I extract that in the `chart`.

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* returncode=Error | rex field=message "Message=.* \((?<apiName>\w+?) -" | lookup My_Client_Mapping client OUTPUT ClientID ClientName Region | chart count over ClientName by apiName

This shows the data like

ClientName	RetrievePaymentsA	RetrievePaymentsB	RetrievePaymentsC
Client A	2	1	4
Client B	2	0	3
Client C	5	3	1

How can I add other fields to the output like this

ClientId	ClientName	Region	RetrievePaymentsA	RetrievePaymentsB	RetrievePaymentsC

Any help will be appreciated.

Re: How to add multiple fields to chart count over

ITWhisperer — Fri, 10 May 2024 17:22:01 GMT

Try doing your lookup after the chart

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* returncode=Error | rex field=message "Message=.* \((?<apiName>\w+?) -" | chart count over client by apiName | lookup My_Client_Mapping client OUTPUT ClientID ClientName Region

Re: How to add multiple fields to chart count over

kuul13 — Fri, 10 May 2024 18:41:09 GMT

Moving lookup after chart fetch nothing.

Re: How to add multiple fields to chart count over

PickleRick — Fri, 10 May 2024 19:39:16 GMT

Did you just move your lookup or did you adjust field names as well?

Re: How to add multiple fields to chart count over

ITWhisperer — Sat, 11 May 2024 07:57:22 GMT

Please share the complete search which is not working.

Also, please include some representative anonymised sample events so we can see what you are dealing with.

Re: How to add multiple fields to chart count over

kuul13 — Sat, 11 May 2024 20:34:55 GMT

I have moved the lookup statement to the end after chart. Here is the latest query that I used. After I move the lookup at the end, I see no data.

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* | rex field=message "Message=.* \((?<apiName>\w+?) -" | chart count over client by apiName | lookup My_Client_Mapping client OUTPUT ClientID ClientName Region

Here is the sample events that I am working with, if that helps:

Time	Event
4/27/24 5:30:37.182 AM	{ "client":"ClientA", "msgtype":"WebService", "priority":2, "interactionid":"1DD6AA27-6517-4D62-84C1-C58CA124516C", "seq":15831, "threadid":23, "message":"TimeMarker: WebService: Sending result @110ms. (retrievePaymentsXY - ID1:123 ID2:ClientId\|1 ID3:01/27/2024-04/27/2024)", "userid":"Unknown" }

My_Client_Mapping lookup table has details of my clients like

Client	ClientId	ClientName	Region
ClientA	1	Client A	Eastern
ClientB	2	Client B	Eastern
ClientC	3	Client C	Western

Re: How to add multiple fields to chart count over

ITWhisperer — Sun, 12 May 2024 09:00:43 GMT

The event you have chosen to show does not match "Message=.*" so you won't get apiName extracted, therefore your chart will return no results (at least for this event).

Your lookup appears to use "Client" as a field name, whereas your event appears to use "client" - fieldnames are case sensitive so these are two different fields.

I hope this helps you resolve your issue.

Re: How to add multiple fields to chart count over

kuul13 — Tue, 14 May 2024 02:37:22 GMT

My bad, sorry that while I was removing the sensitive data, I messed up the event. Here is the actual one that I used:

{
Client:ClientA,
Msgtype:WebService,
Priority:2,
Interactionid:1DD6AA27-6517-4D62-84C1-C58CA124516C,
Seq:15831,
Threadid:23,
message: TimeMarker: MyClient: Result=Success Time=0000.05s Message=No payments found. (RetrievePaymentsXY - ID1:123131 ID2:Site|12313 ID3:05/14/2024-07/12/2024 1|12313),
Userid:Unknown
}

And, the regex works too, here is the working example that would extract the apiName:

https://regex101.com/r/7f9Cnb/1

Re: How to add multiple fields to chart count over

PickleRick — Tue, 14 May 2024 08:16:17 GMT

Ugh. This looks almost like a json structure. Unfortunately your keys and values are not enclosed in quotes so it is not a valid json object. If it were a json object you wouldn't have to worry about regexes because splunk can parse jsons.

And it's best to let it do so instead of trying to fiddle with regexes to handle structured data.

EDIT: OK, earlier you showed some representation of your event and it did include the quotes. So how is it?

Re: How to add multiple fields to chart count over

ITWhisperer — Tue, 14 May 2024 09:14:32 GMT

So, does the search work without the lookup?

Re: How to add multiple fields to chart count over

kuul13 — Tue, 14 May 2024 13:01:50 GMT

Time	Event
4/27/245:30:37.182 AM	{ "Client":"ClientA", "Msgtype":"WebService", "Priority":2, "Interactionid":"1DD6AA27-6517-4D62-84C1-C58CA124516C", "Seq":15831, "Threadid":23, "message":"TimeMarker: MyClient: Result=Success Time=0000.05s Message=No payments found. (RetrievePaymentsXY - ID1:123131 ID2:Site\|12313 ID3:05/14/2024-07/12/2024 1\|12313", "Userid":"Unknown" }

I just want to make sure that I state it right, when I run the following query, I get an output already, so json and fields are all correct. It is just my json was messed up when I massaged it (please ignore) :

index=application_na 
sourcetype=my_logs:hec 
source=my_Logger_PROD  
retrievePayments* 
returncode=Error
| rex field=message "Message=.* \((?<apiName>\w+?) -" 
| lookup My_Client_Mapping Client OUTPUT ClientID ClientName Region 
| chart count over ClientName by apiName

where `chart count over` is at the end. But, when I move the `lookup` statement after `chart`, I don't get any data back.

If I remove the `lookup` the query won't work as `ClientName` is stored in lookup mapping file.

Re: How to add multiple fields to chart count over

ITWhisperer — Tue, 14 May 2024 14:32:07 GMT

What do you get when you try this?

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* returncode=Error | rex field=message "Message=.* \((?<apiName>\w+?) -" | chart count over client by apiName

Re: How to add multiple fields to chart count over

kuul13 — Tue, 14 May 2024 19:44:35 GMT

It says `No results found.`

Re: How to add multiple fields to chart count over

ITWhisperer — Tue, 14 May 2024 22:16:16 GMT

It sounds like either client or apiName hasn't been extracted - can you check e.g.

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* returncode=Error | rex field=message "Message=.* \((?<apiName>\w+?) -" | stats count by client

index=application_na sourcetype=my_logs:hec source=my_Logger_PROD retrievePayments* returncode=Error | rex field=message "Message=.* \((?<apiName>\w+?) -" | stats count by apiName

Re: How to add multiple fields to chart count over

PickleRick — Wed, 15 May 2024 06:22:04 GMT

OK. The proper (and actually the only reasonable I thkink) approach to diagnose "not working" SPL searches is to start from the start and add one step at a time verifying if you're getting desired results at each step of the way.

So first do

index=application_na 
sourcetype=my_logs:hec 
source=my_Logger_PROD  
retrievePayments* 
returncode=Error

and see if you get any results returned at all.

Then add

| rex field=message "Message=.* \((?<apiName>\w+?) -"

And verify that your apiName field is properly extracted.

Then apply

| lookup My_Client_Mapping Client OUTPUT ClientID ClientName Region

and see if the values from the lookup are properly assigned.

If any of those steps fails to produce predicted results, you'll know which step to debug.