topic Re: Stats by the existence of certain fields in Splunk Search

How to achieve stats by the existence of certain fields?

Arishtat — Thu, 11 May 2023 11:31:14 GMT

I have three types of data entries.

{ <Irrelevant field omitted> "parameters": [ { "LicenseNumber": "123456" } ], "eventTimestamp": "2023-05-09T15:23:57+0300", } { <Irrelevant field omitted> "parameters": [ { "Holder_Id": "654321" } ], "eventTimestamp": "2023-05-09T15:23:57+0300", } { <Irrelevant field omitted> "parameters": [ { "Name": "John Doe" } ], "eventTimestamp": "2023-05-09T15:23:57+0300", }

I want to get stats how many by the field parameter field type as in Name:69, Holder_Id:42, LicenseNumber:76.

I thought I'd use eval to create a field by the existence of each parameters, but that doesn't work.

<base_query> | eval group_name = case(isnotnull('parameters{}.Name'), Name, isnotnull('parameters{}.HolderId'), HolderId, isnotnull('parameters{}.LicenseNumber'), LicenseNumber) | stats count by group_name

Re: Stats by the existence of certain fields

gcusello — Thu, 11 May 2023 09:24:29 GMT

Hi @Arishtat,

simplify field names befor an eval command:

| rename 'parameters{}.Name' AS Name 'parameters{}.HolderId' AS HolderId 'parameters{}.LicenseNumber' AS LicenseNumber | eval group_name = case(isnotnull(Name), Name, isnotnull(HolderId), HolderId, isnotnull(LicenseNumber), LicenseNumber) | stats count by group_name

But anyway, if you only want a count of occurrences of each group_name, you could simply use

| stats count by group_name

Ciao.

Giuseppe

Re: Stats by the existence of certain fields

TrangCIC81 — Thu, 11 May 2023 09:32:08 GMT

Can you try the below?Just removed the single quote and added double quotation marks

<base_query> | eval group_name = case(isnotnull(parameters{}.Name), "Name", isnotnull(parameters{}.Holder_Id), "Holder_Id", isnotnull(parameters{}.LicenseNumber), "LicenseNumber") | stats count by group_name

Re: Stats by the existence of certain fields

Arishtat — Thu, 11 May 2023 10:06:20 GMT

Weird, I'm sure I tried adding double quotes at some point to the eval fields. However, the isnotnull parameter has to be in single quote.

So the correct answer is,

| eval group_name = case(isnotnull('parameters{}.Name'), "Name", isnotnull('parameters{}.HolderId'), "HolderId", isnotnull('parameters{}.LicenseNumber'), "LicenseNumber") | stats count by group_name

Thanks for you help.