Recursive search

[ofgem_bird]

I have a script that generates the time offset of a server from it's source, however, what I want to be able to do is reference a time variance for each host to the source.

The idea being that I could get each host variance to the next hop into Splunk. This can be achieved and is working correctly.

What I now need to do is then add the variance time from each host back to it's reference point to the time variance between the reference host and a further reference host.

I thought this would be quite easy but at the moment I seem to be a bit stuck.

I have a series of fields that I can return these are:

_time (timestamp on index for this record),
host (the host being referenced),
NTP (time variance to reference point, + or - number)
OrigRefID (the server that the calculation is based on)

So sample output could look like this:

01/07/2013 14:59:23.000, server1, +0.12345678, server2
01/07/2013 14:59:23.000, serever2, -0.34567812, clock.isc.org

There are records for all the servers in the environment.

What I want to be able to do is add an additional field to each line that references the NTP variance to the external host (in this above example clock.isc.org).

What is the easiest way to perform a search for each of the above and add the data to each return as NTP2?

[DalJeanis]

This created test data into file "dmjtest.csv".

| makeresults | eval mydata=mvappend("111!!!!222!!!!0.007","222!!!!300!!!!0.210","300!!!!300!!!!0.000","333!!!!300!!!!0.017",
  "350!!!!333!!!!0.301","373!!!!333!!!!0.041","444!!!!400!!!!0.104","400!!!!400!!!!0.000","555!!!!666!!!!0.123","666!!!!666!!!!0.000")
| mvexpand mydata | eval mydata=split(mydata,"!!!!") | eval host=mvindex(mydata,0) | eval refhost=mvindex(mydata,1) | eval refdelay=mvindex(mydata,2)
| table host refhost refdelay | outputlookup "dmjtest.csv"

The resulting test data looks like this -

   host refhost refdelay
    111     222   0.007
    222     300   0.210
    300     300   0.000
    333     300   0.017
    350     333   0.301
    373     350   0.041
    444     400   0.104
    400     400   0.000
    555     666   0.123
    666     666   0.000

This parsed that data into the final accumulated delay

 inputlookup "dmjtest.csv"
| eval finhost=case(host==refhost,host,true(),null())
| eval findelay=case(host==refhost,refdelay,true(),null()) 
| eval ImDone=case(host==refhost,1,true(),0) 
| table host refhost refdelay finhost findelay
| eval orighost=host, origref=refhost, origdelay=refdelay
| rename refhost as sumhost, refdelay as sumdelay 

| eval host=sumhost
| join type=left host  [ | inputlookup "dmjtest.csv" | rename refdelay as stepdelay, refhost as stephost]
| eval finhost=coalesce(finhost,if(sumhost==stephost,sumhost,null()))
| eval findelay=coalesce(findelay,if(sumhost==stephost,sumdelay,null()))
| eval sumhost=coalesce(stephost, sumhost)
| eval sumdelay=coalesce(sumdelay+stepdelay,sumdelay)

| eval host=sumhost
| join type=left host  [ | inputlookup "dmjtest.csv" | rename refdelay as stepdelay, refhost as stephost]
| eval finhost=coalesce(finhost,if(sumhost==stephost,sumhost,null()))
| eval findelay=coalesce(findelay,if(sumhost==stephost,sumdelay,null()))
| eval sumhost=coalesce(stephost, sumhost)
| eval sumdelay=coalesce(sumdelay+stepdelay,sumdelay)

| eval host=sumhost
| join type=left host  [ | inputlookup "dmjtest.csv" | rename refdelay as stepdelay, refhost as stephost]
| eval finhost=coalesce(finhost,if(sumhost==stephost,sumhost,null()))
| eval findelay=coalesce(findelay,if(sumhost==stephost,sumdelay,null()))
| eval sumhost=coalesce(stephost, sumhost)
| eval sumdelay=coalesce(sumdelay+stepdelay,sumdelay)

| table orighost origref origdelay finhost findelay
| rename orighost as host, origref as refhost, origdelay as refdelay

You will note that the middle part of the search is iterative, rather than recursive. Just copy that part of the code however many levels deep you think the recursion might be.

The resulting output looks like this -

host  refhost refdelay finhost   findelay
111     222     0.007   300     0.217
222     300     0.210   300     0.210
300     300     0.000   300     0.000
333     300     0.017   300     0.017
350     333     0.301   300     0.318
373     350     0.041   300     0.359
444     400     0.104   400     0.104
400     400     0.000   400     0.000
555     666     0.123   666     0.123
666     666     0.000   666     0.000

---

note - the output has been reformatted to look pretty in text.