Splunk Search

What is my error and what is the best practice for a situation where I need to join 2 log files that only have time as the same field?

sdorich
Communicator

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

sdorich
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...