How to add multiple fields to chart count over

[kuul13]

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.

[ITWhisperer]

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 

[kuul13]

Moving lookup after chart fetch nothing.

[ITWhisperer]

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.

[PickleRick]

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

[kuul13]

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

[ITWhisperer]

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.

[kuul13]

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

[ITWhisperer]

So, does the search work without the lookup?

[PickleRick]

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?

[kuul13]

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.

[PickleRick]

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.

[ITWhisperer]

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

[kuul13]

It says `No results found.`

[ITWhisperer]

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

or

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