topic Re: Field appearing as null in table when too long in Splunk Search

Field appearing as null in table when too long

sk — Thu, 07 Jan 2021 21:58:30 GMT

I am searching for queries that are running over a certain amount of time and displaying start/end time and query in a table.

For some rows, the query is null but when I look at the event, the field has a value. The value is very long though.

Is there a limit on the number of characters that can be displayed in a field on a table?

I have tried using substring to bring back only a few characters. This will work for the values already being displayed but the value I want to see is still null.

TIA

Re: Field appearing as null in table when too long

bowesmana — Thu, 07 Jan 2021 22:02:23 GMT

Can you provide your query and an example of the data. There should be no real limits to the field size, so if the field is null, there is something else going on.

Re: Field appearing as null in table when too long

sk — Thu, 07 Jan 2021 22:27:15 GMT

Query (with some data blocked out):

index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start, "protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query" as "Query" | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=5 | table Start End "Query" Query_Duration

In the raw data on the search results

"protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query"
starts with

"MERGE INTO\n `project.dataset.table`... "

But in the table format, "Query" ends up as null (only for some rows!)

Is the whole "Query" field required? If so I'll have to block out quite a bit of info. Is there anything I should look for in this "Query" field to troubleshoot?

Re: Field appearing as null in table when too long

sk — Fri, 08 Jan 2021 00:20:06 GMT

Also, the query value actually ends with

" number,\n ...(string is too long)" in the source system logs.

Re: Field appearing as null in table when too long

bowesmana — Fri, 08 Jan 2021 02:53:04 GMT

@sk

It sounds like your field extraction is not extracting the protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query field correctly.

Is this raw data JSON? What is the size of the raw data length? By default Splunk will auto extract fields from a JSON payload up to the first 5000 characters.

From limits.conf

[spath] # Number of characters to read from an XML or JSON event when # auto extracting. extraction_cutoff = 5000 extract_all = true

If this is the issue, then you will need to do manual spath statements to extract the JSON you want, e.g.

| spath input=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query path=query

If it's not JSON, it's still about how your data is being extracted from the _raw to the fields and there will be something in the data that is breaking whatever rules you have.

Re: Field appearing as null in table when too long

sk — Sat, 09 Jan 2021 00:00:52 GMT

Thanks for your information @bowesmana

I have tried

index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start | spath input=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query output=Query path=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=10 | table Start End "Query" Query_Duration

index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start | spath input=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query path=Query | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=10 | table Start End "Query" Query_Duration

But now the Query column on the table is null for all rows. Am I misunderstanding the spath parameters?

Re: Field appearing as null in table when too long

bowesmana — Mon, 11 Jan 2021 00:43:12 GMT

It depends on what the fields actually are, but using _raw will probably work, as long as the data is JSON, e.g.

| spath input=_raw path=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query output=Query

which would get the value of the lowest level query element

Re: Field appearing as null in table when too long

sk — Mon, 11 Jan 2021 20:02:21 GMT

@bowesmana Thank you kindly! This works great!