So first I'm wondering what my error in the following search is:
eventtype=sis_daily | join _time [search eventtype=sis_daily] | eval status = if((_time > start_time) AND (_time < end_time), "alert", "ok") | stats count by host, sis_target, sis_monitorRun_status | eval sustr(sis_target, 2, len(sis_target)) | sort sis_target | rename host as "SiteScope Instance", sis_target as Host, sis_monitorRun_status as "Monitor Status"
The search works if I take out "| eval sustr(sis_target, 2, len(sis_target))" but I want to get rid of the first character in each of the fields values.
Second, I've found a lot of similar questions/comments about joining 2 log files together. I've noticed that it is very expensive and I'd like to use stats or transaction instead (as suggested by many fellow Splunkers). The problem is is join has been the only thing that gives me the results I expected. When using transaction for example I'd get results but when I put them in a table they were skewed (the first few rows came from events in one log file and the next rows were their corresponding values that came from events in the second log file - I wanted one row to contain data from both log files). So I guess I'm just asking if this is possible when the only "key" I have is time or if I have to settle with the join command I used above. Or in other words, what is the "best practice" for this kind of situation?
Thanks in advance!
You are using incorrect syntax in the eval-substr stmt. Correct query will be
eventtype=sis_daily | join _time [search eventtype=sis_daily] | eval status = if((_time > start_time) AND (_time < end_time), "alert", "ok") | stats count by host, sis_target, sis_monitorRun_status | eval sis_target=substr(sis_target, 2) | sort sis_target | rename host as "SiteScope Instance", sis_target as Host, sis_monitorRun_status as "Monitor Status"
Also, since you're joining same eventtypes, I am not sure if that was required here. It seems you just wanted to compare _time with start_time and end_time, which you can do without join. Try below:-
eventtype=sis_daily | eval status = if((_time > start_time) AND (_time < end_time), "alert", "ok") | stats count by host, sis_target, sis_monitorRun_status | eval sis_target=substr(sis_target, 2) | sort sis_target | rename host as "SiteScope Instance", sis_target as Host, sis_monitorRun_status as "Monitor Status"
Joins are expensive and should be avoided if possible. But what should replace join, it all depends on the requirement.
I just tried that search and got no results back. I'll try messing around with eval again but I did try this yesterday and didn't have luck (hence why I decided to use join). I know you don't have my data so it might be difficult to answer my second question. But thanks for answering my first - I did just use the wrong syntax.