topic From search to Data Model in Splunk Search

From search to Data Model

kukasky — Wed, 11 Sep 2024 07:58:11 GMT

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?

Re: From search to Data Model

PickleRick — Wed, 11 Sep 2024 09:25:43 GMT

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.

Re: From search to Data Model

kukasky — Wed, 11 Sep 2024 11:17:40 GMT

| 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

Re: From search to Data Model

PickleRick — Wed, 11 Sep 2024 12:01:51 GMT

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?