Splunk Search

From search to Data Model

kukasky
Loves-to-Learn

Hi, i have problem with Data model search.

This is my SPL:

|datamodel Network_Resolution_DNS_v2 search| search DNS.message_type=Query |rename DNS.query as query
| fields _time, query
| streamstats current=f last(_time) as last_time by query
| eval gap=last_time - _time
| stats count avg(gap) AS AverageBeaconTime var(gap) AS VarianceBeaconTime BY query
| eval AverageBeaconTime=round(AverageBeaconTime,3), VarianceBeaconTime=round(VarianceBeaconTime,3)
| sort -count
| where VarianceBeaconTime < 60 AND count > 2 AND AverageBeaconTime>1.000
| table query VarianceBeaconTime count AverageBeaconTime

and it's work fine but slowly, so i would like to change to Data Model.

How looks like query ? 
I have DM model DNS_v2 and it's work for another queries, but not for this.


| tstats summariesonly=true values(DNS.query) as query FROM datamodel="DNS_v2" where DNS.message_type=Query groupby _time
| mvexpand query
| streamstats current=f last(_time) as last_time by query
| eval gap=(last_time - _time)
| stats count avg(gap) AS AverageBeaconTime var(gap) AS VarianceBeaconTime BY query
| eval AverageBeaconTime=round(AverageBeaconTime,3), VarianceBeaconTime=round(VarianceBeaconTime,3)
| sort -count
| where VarianceBeaconTime < 60 AND count > 2 AND AverageBeaconTime>1.000
| table query VarianceBeaconTime count AverageBeaconTime

Has anyone had this problem before?

 

Labels (1)
Tags (2)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

What is a "DNS_v2" datamodel? It's not one of the CIM-defined ones.

Your original search uses

|datamodel Network_Resolution_DNS_v2 search

whereas your tstats use

| tstats summariesonly=true values(DNS.query) as query FROM datamodel="DNS_v2" [...]

Few more hints:

1. Use preformatted paragraph style or a code block to paste SPL - it helps in reability and prevents the forum interface from rendering some text as emojis and such.

2. What do you mean by "doesn't work"? Do you get an error? Or you simply get different results than expected? If so, how they differ?

3. There are two typical approaches to debugging SPL - either build it from the start adding commands one by one until they stop yielding proper results or start with the whole search and remove commands from the end one by one until they start producing proper results - then you know which step is the problematic one.

4. Often it's much easier for people to help you when you provide sample(s) of your data and describe what you want to do with it than posting some (sometimes fairly complicated) SPL without additional comments as to what you want to achieve.

0 Karma

kukasky
Loves-to-Learn

 

 

 

| tstats summariesonly=true values(DNS.query) as query FROM datamodel="Network_Resolution_DNS_v2" where DNS.message_type=Query groupby _time
| mvexpand query
| streamstats current=f last(_time) as last_time by query
| eval gap=(last_time - _time)
| stats count avg(gap) AS AverageBeaconTime var(gap) AS VarianceBeaconTime BY query
| eval AverageBeaconTime=round(AverageBeaconTime,3), VarianceBeaconTime=round(VarianceBeaconTime,3)
| sort -count
| where VarianceBeaconTime < 60 AND count > 2 AND AverageBeaconTime>1.000
| table query VarianceBeaconTime count AverageBeaconTime

 

 

 

Sorry when i have copied i pasted wrong datamodel. This is CIM model, but i duplicate this model and add some additional fields, but for this query i need only field query and time.

Original query is from 
https://www.splunk.com/en_us/blog/security/detect-hunt-dns-exfiltration.html?locale=en_us

0 Karma

PickleRick
SplunkTrust
SplunkTrust

OK. What does "doesn't work" mean here? And do you get any results from the initial tstats search?

Stupid question - is your datamodel even accelerated?

0 Karma
Get Updates on the Splunk Community!

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...