Splunk Cloud Platform

How to replace a numeric value with string?

Sheril
Engager

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!

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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 😁

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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 😁

 

Sheril
Engager

That worked! 🕺

0 Karma
Get Updates on the Splunk Community!

ATTENTION!! We’re MOVING (not really)

Hey, all! In an effort to keep this Slack workspace secure and also to make our new members' experience easy, ...

Splunk Admins: Build a Smarter Stack with These Must-See .conf25 Sessions

  Whether you're running a complex Splunk deployment or just getting your bearings as a new admin, .conf25 ...

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...