Splunk Search

Best Practices to join two child objects of a data model

sanjay_shrestha
Contributor

We have a situation where we need to join two child objects of a data model. Both child objects have separate index and host.

e.g.

 ProjectInformation (Datamodel Object)
                                 _time
                                 host
                                ..........
                                 ProjectId (Extracted Field)

             FromIndexOne (Datamodel Child Object)
                                 _time
                                 host
                                 ProjectId (Inherited Field)                             
                                ............
                                CalculatedField1
                                CalculatedField2

             FromIndexTwo (Datamodel Child Object)
                                 _time
                                 host
                                 ProjectId (Inherited Field)                             
                                ............
                                CalculatedField3
                                CalculatedField4

We would like to have result with following fields:

    _time;  ProjectId; CalculatedFiled1; CalculatedFiled2; CalculatedFiled3; CalculatedFiled4
0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi sanjay.shrestha,

take a look at this run everywhere example to get an idea how this can be done, it uses Splunk internal_server data model and two of its child objects:

| tstats values(server.licenser.quota.gb) AS gb values(server.scheduler.scheduled_reports.scheduled_time) AS scheduled_time from datamodel="internal_server" by _time 
| streamstats last(gb) AS last_gb last(scheduled_time) AS last_schedule 
| stats count by _time last_gb last_schedule 
| fields - count

To break this down I'll explain each search pipe:

Obviously we need to get something first, so we get gb from the server.licenser.quota child / nodename and scheduled_time from the server.scheduler.scheduled_reports child / nodename (makes no sense I know, but this is only to show how you can do it) :

| tstats values(server.licenser.quota.gb) AS gb values(server.scheduler.scheduled_reports.scheduled_time) AS scheduled_time from datamodel="internal_server" by _time

Next use the result in streamstats to get rid of those empty results:

| streamstats last(gb) AS last_gb last(scheduled_time) AS last_schedule 

use this result in a stats remove the count field and here we go:

| stats count by _time last_gb last_schedule 
| fields - count

This is just an example, modify it to your needs to match your use case.

Hope this helps ...

cheers, MuS

View solution in original post

MuS
SplunkTrust
SplunkTrust

Hi sanjay.shrestha,

take a look at this run everywhere example to get an idea how this can be done, it uses Splunk internal_server data model and two of its child objects:

| tstats values(server.licenser.quota.gb) AS gb values(server.scheduler.scheduled_reports.scheduled_time) AS scheduled_time from datamodel="internal_server" by _time 
| streamstats last(gb) AS last_gb last(scheduled_time) AS last_schedule 
| stats count by _time last_gb last_schedule 
| fields - count

To break this down I'll explain each search pipe:

Obviously we need to get something first, so we get gb from the server.licenser.quota child / nodename and scheduled_time from the server.scheduler.scheduled_reports child / nodename (makes no sense I know, but this is only to show how you can do it) :

| tstats values(server.licenser.quota.gb) AS gb values(server.scheduler.scheduled_reports.scheduled_time) AS scheduled_time from datamodel="internal_server" by _time

Next use the result in streamstats to get rid of those empty results:

| streamstats last(gb) AS last_gb last(scheduled_time) AS last_schedule 

use this result in a stats remove the count field and here we go:

| stats count by _time last_gb last_schedule 
| fields - count

This is just an example, modify it to your needs to match your use case.

Hope this helps ...

cheers, MuS

sanjay_shrestha
Contributor

Thanks Michael.

0 Karma

kamalm1
Explorer

values() function gives unique values only, what if we need all the values of a child object field and do stats over that.

0 Karma

kamalm1
Explorer

list() function doesnot work with tstats

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...