Dashboards & Visualizations

2 searches, 1 chart

gnovak
Builder

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

Tags (1)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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
Builder

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

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...