Field appearing as null in table when too long

[sk]

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

[bowesmana]

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.

[sk]

Also, the query value actually ends with 

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

[sk]

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?

[bowesmana]

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

[sk]

Thanks for your information @bowesmana 

I have tried 

1) 

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

2)

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?

[bowesmana · Accepted solution]

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 

[sk]

@bowesmana Thank you kindly! This works great!