2 searches, 1 chart

[gnovak]

Ok so I've got 2 searches that currently I have each displayed separately in their own charts. However I want to have the results from both of these searches displayed in one chart.

The searches are:

sourcetype="Cron_SendNotificationEmail" (source="*asia*" OR source="*info*" OR source="*org*" OR source="*mobi*" OR source="*me*" OR source="*aero*" OR source="*cctld*") earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55) | rex field=_raw "send_to_email ?\[(?P<send_to_email>\S+)\]" max_match=1000 | search send_to_email="*" | timechart count(send_to_email) as TotalEmailsSent

And....

sourcetype="Cron_CheckRegistrarThreshold" (source="*asia*" OR source="*info*" OR source="*org*" OR source="*mobi*" OR source="*me*" OR source="*aero*" OR source="*cctld*") earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55) | rex "(?P<inserting_a_record>Inserting a record.*)" max_match=1000 | search inserting_a_record="*" | timechart count(inserting_a_record) as TotalEmailsToSend

I tried to have one search for both of the above searches. However the end results I had did not calculate the numbers correctly.

I tried using the "set union" command to combine the searches together but that also did not produce the results i expected. For example:

| set union [search sourcetype="Cron_CheckRegistrarThreshold" (source="*asia*" OR source="*info*" OR source="*org*" OR source="*mobi*" OR source="*me*" OR source="*aero*" OR source="*cctld*") earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55)| rex "(?P<inserting_a_record>Inserting a record.*)" max_match=1000 | search inserting_a_record="*"] [search sourcetype="Cron_SendNotificationEmail" (source="*asia*" OR source="*info*" OR source="*org*" OR source="*mobi*" OR source="*me*" OR source="*aero*" OR source="*cctld*") earliest=-7d@d NOT (day_hour=23 AND day_minute>=55) | rex field=_raw "send_to_email?\[(?P<send_to_email>\S+)\]" max_match=1000 | search send_to_email="*"] | timechart count(inserting_a_record) as TotalEmailsToSend count(send_to_email) as TotalEmailsSent

Both of the searches are very similar but they are looking at different logs, thus the sourcetypes being different.

I haven't had much luck trying to get this to work. I get results, but it's either is graphs only TotalEmailsToSend and not TotalEmailSent or it graphs both but the numbers are not correct.

The end result is that both TotalEmailsToSend and TotalEmailsSent will match in their numbers.

Any ideas? or did I just loose everyone? 😛

[gkanapathy · Accepted solution]

Seems to me that

((sourcetype="Cron_SendNotificationEmail" "[*]")
 OR 
 (sourcetype="Cron_CheckRegistrarThreshold" "Inserting a record*"))
(source="*asia*" OR source="*info*" OR source="*org*" OR 
 source="*mobi*" OR source="*me*" OR source="*aero*" OR source="*cctld*")
earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55) 
| rex "send_to_email ?\[(?P<send_to_email>\S+)\]" max_match=1000 
| rex "(?P<inserting_a_record>Inserting a record.*)" max_match=1000 
| timechart 
   sum(eval(if(sourcetype=="Cron_SendNotificationEmail",
               mvcount(send_to_email),
               0 ))) 
    as TotalEmailsSent
   sum(eval(if(sourcetype=="Cron_CheckRegistrarThreshold",
               mvcount(inserting_a_record),
               0 ))) 
    as TotalEmailsToSend

would work.

[gnovak]

this worked great. I was glad to see an example of using multiple sourcetypes and eval. I tried using them in the past and always got the format of the commands mixed up

[dwaddle]

You might be able to do this with append. Something like this should work, but is probably NOT the best performing way of doing it.

sourcetype="Cron_SendNotificationEmail" 
(source="*asia*" OR source="*info*" OR source="*org*" 
  OR source="*mobi*" OR source="*me*" OR source="*aero*" 
  OR source="*cctld*") 
earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55) 
| rex field=_raw "send_to_email ?\[(?P<send_to_email>\S+)\]" max_match=1000 
| search send_to_email="*" 
| append [
  sourcetype="Cron_CheckRegistrarThreshold" 
   (source="*asia*" OR source="*info*" OR source="*org*" 
     OR source="*mobi*" OR source="*me*" OR source="*aero*" 
     OR source="*cctld*") 
  earliest=-7d@d latest=@d NOT (day_hour=23 AND day_minute>=55) 
  | rex "(?P<inserting_a_record>Inserting a record.*)" max_match=1000 
  | search inserting_a_record="*" | fields inserting_a_record ]
| timechart count(inserting_a_record) as TotalEmailsToSend, 
  count(send_to_email) as TotalEmailsSent

Given the large similarity between your two searches, you might be able to refactor this into a single, simpler search. The above technique works for me when graphing two related, but different, data sets. But, it's not very fast.