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?
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.