How to replace a numeric value with string?

Sheril — Sat, 24 Sep 2022 00:12:18 GMT

I am pushing DNS logs to Splunk Cloud and I am noticing the QueryType is in numeric format, I would like to see that in string format

Sample Log:

{"ColoID":378,"Datetime":"2022-09-23T23:55:23Z","DeviceID":"df34037e","DstIP":"xx.xx.xx.xx","DstPort":0,"Email":"non_identity@ec.com","Location":"London","Policy":"","PolicyID":"","Protocol":"https","QueryCategoryIDs":[26,81],"QueryName":"europe-west9-a-osconfig.googleapis.com","QueryNameReversed":"com.googleapis.europe-west9-a-osconfig","QuerySize":67,"QueryType":28,"RData":[{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIIAAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIHwAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIFQAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIIQAAAAAAACAK"}],"ResolverDecision":"allowedOnNoPolicyMatch","SrcIP":"xx.xx.xx.xx","SrcPort":0,"UserID":"723f7"}

In the above log you would notice "QueryType":28, I'd like to replace 28 with a string - AAAA, other DNS query types can be found in https://en.wikipedia.org/wiki/List_of_DNS_record_types

Is there a way I could replace or append the query types string instead of the numeric value that is showing up in the logs by using techniques like lookup or Join?

Desired Log: (only QueryType is changed from 28 to AAAA)

{"ColoID":378,"Datetime":"2022-09-23T23:55:23Z","DeviceID":"df34037e","DstIP":"xx.xx.xx.xx","DstPort":0,"Email":"non_identity@ec.com","Location":"London","Policy":"","PolicyID":"","Protocol":"https","QueryCategoryIDs":[26,81],"QueryName":"europe-west9-a-osconfig.googleapis.com","QueryNameReversed":"com.googleapis.europe-west9-a-osconfig","QuerySize":67,"QueryType":AAAA,"RData":[{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIIAAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIHwAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIFQAAAAAAACAK"},{"type":"28","data":"F2V1cm9wZS13ZXN0OS1hLW9zY29uZmlnCmdvb2dsZWFwaXMDY29tAAAcAAEAAADdABAqABRQQAkIIQAAAAAAACAK"}],"ResolverDecision":"allowedOnNoPolicyMatch","SrcIP":"xx.xx.xx.xx","SrcPort":0,"UserID":"723f7"}

Thanks!

Re: How to replace a numeric value with string?

bowesmana — Sat, 24 Sep 2022 03:16:01 GMT

Create yourself a CSV lookup file (e.g. DNS_record_types.csv) containing the fields you want to lookup, let's assume you have

QueryType,Type
28,AAAA

Then in your SPL do

| lookup DNS_record_types.csv QueryType

and it will output the "Type" field from your lookup into your data.

Quick note: JOIN is almost NEVER a solution in Splunk and certainly never for lookups. Leave any SQL concepts behind when working with Splunk SPL 😁

Re: How to replace a numeric value with string?

Sheril — Wed, 28 Sep 2022 23:08:13 GMT

That worked! 🕺

topic Re: How to replace a numeric value with string? in Splunk Cloud Platform

How to replace a numeric value with string?

Re: How to replace a numeric value with string?

Re: How to replace a numeric value with string?