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!
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 😁
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 😁
That worked! 🕺