Issue with Splunk Query Stats not brining in all values

Question

I have a log which has below lines in it:

 "Results":{"Elapsed":"0","Message":"No of Application to Obsolete in Teradata : 4","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":"Total Application Asset in Teradata : 1696","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":"Total Application count from SPAM : 1694","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":" Application/s to Obsolete in Teradata : [PA00007618, PA00007617, PA00007619, PA00007620]","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}

I want the output to have the below fields like a summary and not like in 4 columns.

ExecutionDate   Host          Summary
 02-24-2021   Production     No of Application to Obsolete in Teradata : 4
                            Total Application Asset in Teradata : 1696
                            Total Application count from SPAM : 1694
                            Application/s to Obsolete in Teradata : [PA00007618, 
                            PA00007617, PA00007619, PA00007620]

I have built below query but it's only giving me one record :

ExecutionDate Host Total Application count from SPAM : 1694


index=hdt  sourcetype=Teradata_SPAM_logs  | fields -_raw
| where match(_raw, "Host_cdc") and (match(_raw,"Total\sApplication\scount\sfrom\sSPAM\s*") 
OR match(_raw,"Total\sApplication\sAsset\sin\sTeradata\s*") 
OR match(_raw,"No\sof\sApplication\sto\sObsolete\sin\sTeradata\s*") 
OR match(_raw,"List\sof\sApplications\sin\sTeradata\sto\sbe\smarked*") 
)
| rex "(?<Summary>\"Message\":(.*\w+)\s:.*)" 
| rex "(?<Host>\"Host\":(.*\",))" 
| rex "(?<ExecutionDate>\d{4}\-\d{2}\-\d{2})" 
| rex field=Summary mode=sed "s/\"Message\":\"/ /"
| rex field=Summary mode=sed "s/\"TraceLevel.*/ /"
| rex field=Summary mode=sed "s/\".*$//"
| rex field=Host mode=sed "s/\"Channel.*/ /" 
| rex field=Host mode=sed "s/\"Host\":\"/ /" 
| rex field=Host mode=sed "s/\/.*/ /"
| eval Host = replace(Host,"Host_cdc.cdc.CRAB.com", "PRODUCTION") 
| eval Host = replace(Host,"Host_DEV.cdc.CRAB.com", "PROFILING") 
| eval Host = replace(Host,"Host_PP.cdc.CRAB.com", "VALIDATION") 
| stats  values(Summary) as Summary by ExecutionDate, Host
| where isnotnull(Summary)

Can anyone tell me where is the problem here?

Yes, I have added that in the description of this question and also the format I am looking for is like --I want all these in a single column — Amit, Feb 26 '21 at 09:15

score 0 · Answer 1 · answered Feb 26 '21 at 11:42

Based on your simplyfied example (i of course dont have all your other data so cannot run your full query but using the data you provided

"Results":{"Elapsed":"0","Message":"No of Application to Obsolete in Teradata : 4","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":"Total Application Asset in Teradata : 1696","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":"Total Application count from SPAM : 1694","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}
"Results":{"Elapsed":"0","Message":" Application/s to Obsolete in Teradata : [PA00007618, PA00007617, PA00007619, PA00007620]","TraceLevel":"INFO"},"Security":{"Vendor":"CRAB"}}

and the query

source="stack.csv" host="localhost" index="stack" 
| rex "Message\":\"(?<message_value>[^\"]+)\""
| table message_value

i get the output

 Application/s to Obsolete in Teradata : [PA00007618, PA00007617, PA00007619, PA00007620]
Total Application count from SPAM : 1694
Total Application Asset in Teradata : 1696
No of Application to Obsolete in Teradata : 4

you should then be able to just put that in your stats output with the vairable name message_value

Thanks@Chris Can you please explain a little about your query? and from which part of your query should i try to check in actual log? — Amit, Feb 26 '21 at 13:31
So here I have just stuck your data in a csv file called stack.csv, i have uploaded the data into index called stack and from my localhost. So i am searching that index, and host for data form the source file. I am then using rex command to look in each event to find the value of the message field. So do this i look for the `"message":"` string in the event and then use capture group to capture the next part which is the value . For this I tell it to capture into the group all chars that are not a `"`. once it reaches a `"` it will stop. It will then store this value in the var message_value — Chris Doyle, Feb 26 '21 at 13:52
So you can then use this field in the rest of your query when you want to refer to this value — Chris Doyle, Feb 26 '21 at 13:52

score 0 · Answer 2 · answered Feb 26 '21 at 12:30

This should work based on your example input. Of course, you may need to fiddle with it to adapt it to your event stream.

| makeresults 
| eval myevent="\"Results\":{\"Elapsed\":\"0\",\"Message\":\"No of Application to Obsolete in Teradata : 4\",\"TraceLevel\":\"INFO\"},\"Security\":{\"Vendor\":\"CRAB\"}}
\"Results\":{\"Elapsed\":\"0\",\"Message\":\"Total Application Asset in Teradata : 1696\",\"TraceLevel\":\"INFO\"},\"Security\":{\"Vendor\":\"CRAB\"}}
\"Results\":{\"Elapsed\":\"0\",\"Message\":\"Total Application count from SPAM : 1694\",\"TraceLevel\":\"INFO\"},\"Security\":{\"Vendor\":\"CRAB\"}}
\"Results\":{\"Elapsed\":\"0\",\"Message\":\"Application/s to Obsolete in Teradata : [PA00007618, PA00007617, PA00007619, PA00007620]\",\"TraceLevel\":\"INFO\"},\"Security\":{\"Vendor\":\"CRAB\"}}"


 | rex mode=sed field=myevent "s/([\r\n]+)/##LF##/g" | makemv myevent delim="##LF##" | rename myevent as myevent_new | mvexpand myevent_new
 
 
 |  rex field=myevent_new "Results\":(?<json_event>.*)"
 
 | spath input=json_event
 | eval ExecutionDate=strftime(now(),"%d-%m-%Y")
 | eval Host="Production"
 | stats values(Message) AS Summary by ExecutionDate Host

And the output:

Thanks @Honky -- From which part of the query do u want me to try? From Rex field? — Amit, Feb 26 '21 at 13:24

Issue with Splunk Query Stats not brining in all values

2 Answers2