I am trying to find a query that can calculate the time difference between 2 events. It should give me the time for the devname, but notice in the screenshot that the same device can have the same set of events happen again and each clubbed, two event instance should be treated separately.
The time calculation needs to start when
msg="*Backup to Master*" and conclude when
msg="*Master to Backup*"
Here are two queries I tried unsuccessfully. Any help is much appreciated!
index=pci_logid=0103027001 | transaction devname startswith=eval(msg="*Backup to Master*") endswith=eval(msg="*Master to Backup*") | search eventcount = 2 | table devname duration _time index=pci_bjs_index msg="*Master to Backup*" OR msg="*Backup to Master*" | transaction devname startswith=eval(msg="*Backup to Master*") endswith=eval(msg="*Master to Backup*") keepevicted | eval StartTime=_time | eval EndTime=_time+duration | table devname StartTime, EndTime
Thanks in advance for input! Please also notice the repeats of devnames aforementioned.
index=pci_logid=0103027001 | transaction devname startswith="Backup to Master" endswith="Master to Backup" maxevents=2 | table devname duration _time
... or this...
index=pci_logid=0103027001 | sort 0 devname - _time | transaction devname startswith="Backup to Master" endswith="Master to Backup" maxevents=2 | table devname duration _time
Sorry, neither work and actually I tried the first one you shared before. Keep in mind the msg field in the logs don't actually start with those values, it contains.
Your comment trails off. If you need to post code, then make sure to mark it with the code button (101 010) or put it inside grave accents (under the tilde key ~ on an American keyboard) so that the web interface doesn't treat it as HTML/formatting instructions.
By removing the field name, that should have looked for the plain language anywhere in the _raw. I played around with some test code, and found that in plain quotes, the search terms are treated as search terms, so "Backup to Master" and "Master to Backup" are acting exactly the same.
| transaction devname endswith=eval(like(_raw,"%Master to Backup%")) startswith=eval(like(_raw,"%Backup to Master%"))
...and here's some run-anywhere code to show it works
| makeresults | eval mydata = "A,1 A,2 B,1 C,1 C,2 A,1 D,1 B,2 A,2 D,2" | makemv mydata | mvexpand mydata | streamstats count | eval _time=_time+count | rex field=mydata "(?<devname>.*),(?<rectype>\d)" | eval BTM=if(rectype=1,"Backup to Master ","Master to Backup ") | eval _raw = strftime(_time,"%c")." ".devname." changes state from ".BTM." blah blah " | table _time _raw devname count | sort 0 devname - _time | transaction devname endswith=eval(like(_raw,"%Master to Backup%")) startswith=eval(like(_raw,"%Backup to Master%")) keepevicted=t mvlist=true
Thanks for the feedback DalJeanis on my commenting and also for your help.
Can you please direct me to a book or document where I can get to be as good as you?
I have "Exploring Splunk" but if you have any resources to share, please do!
Direct application is key to learning. I have only a few hundred hours in on splunk at the moment, but that's on top of many thousands of hours of other kinds of ETL and data analysis. For me, much of the effort is in porting my knowledge over from SQL Server, Statistics, DB2, relational modeling, MS Access, Excel, and what we used to call "Data Processing" back in the days that the Internet was two tin cans and a piece of string. And literally half of what I am learning is finding out which efficient methods in relational databases turn out to be highly inefficient in splunk.
Here's what you do - look at answers.splunk.com, and look for open questions that you almost know the answer to. Research and present the full answer. Make it as clear as you possibly can, and present run-anywhere test data if the question is at all complex. (Just learning the simple ways to make decent test data is a good part of the battle here.)
Repeat, repeat, repeat.
Also, follow the three or four people who present the clearest and simplest (most elegant) answers to questions. If someone posts an answer that works -- but that you don't understand -- then set up some test data and run through the answer, adding one line at a time, and see what effect each line has on the data. Do not let the question go until you understand why the solution works.
Top posters on my list are somesoni2 and woodcock. Somesoni2 is hands-down the best explainer that I know, with clear and elegant answers. Woodcock has some strong opinions that I often disagree with, but his way of thinking about problems is totally different from mine so I learn much more from how he approaches a solution. I'd like to think I'm in their class, at least in terms of forum posts.
Honorable mentions to richgalloway, and jkat54.
Repeat, repeat, repeat.
Oh, and don't forget to actually apply your skills in real life.
Repeat, repeat, repeat.