Reporting

How to combine multiple reports into one report from same index?

limalbert
Path Finder

Hey guys,

Is it possible to combine 3 reports (bar charts) from the same index into one report (bar chart)?

Thanks in advance!

Edit:
Report 1:

index=app_fig keywordA keywordB* keywordC
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "type\=(?<field1>[^,]*)"
| rex "userId:(?<user>\w*)\_"
| rex "workOrder=(?<woNum>\w*)\," 
| search referral=*
| chart dc(woNum) by currentDate, field1

Report 2:
index=app_fig keywordD keywordE keywordF

| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "fieldType=(?<field1>[^,]*)"
| rex "userId:(?<user>\w*)\_"
| rex "workOrderNumber=(?<woNum>\w*)\,"
| chart dc(woNum) by currentDate, field1

Report 3:

index=app_fig keywordG keywordH
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
| rex "fieldName\":\"(?<field1>[^\"]*)"
| rex "userId:(?<user>\w*)\_"
| rex "wo:(?<woNum>\w*)_" 
| chart dc(woNum) by currentDate, field1
Labels (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
 | rex "(type|fieldType)\=(?<field11>[^,]*)"
 | rex "fieldName\":\"(?<field12>[^\"]*)"
 | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
 | rex "wo:(?<woNum2>\w*)_"  
| eval field1=coalesce(field11,field12)
| eval woNum=coalesce(woNum1,woNum2)
| chart dc(woNum) by currentDate, field1

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
| rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
 | rex "(type|fieldType)\=(?<field11>[^,]*)"
 | rex "fieldName\":\"(?<field12>[^\"]*)"
 | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
 | rex "wo:(?<woNum2>\w*)_"  
| eval field1=coalesce(field11,field12)
| eval woNum=coalesce(woNum1,woNum2)
| chart dc(woNum) by currentDate, field1

limalbert
Path Finder

Hey,

This works! But, I'm getting NULL from field11, and VALUE from field12.
I have never used coalesce command before. What does this actually do?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The coalesce (like oracle coalesce) takes the first non-null value. If instead of null values it has literal NULL string, try this variation.

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
 | rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
  | rex "(type|fieldType)\=(?<field11>[^,]*)"
  | rex "fieldName\":\"(?<field12>[^\"]*)"
  | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
  | rex "wo:(?<woNum2>\w*)_"  
 | eval field1=if(searchmatch("keywordG keywordH"), field12,field11)
 | eval woNum=if(searchmatch("keywordG keywordH"), woNum2,woNum1)
 | chart dc(woNum) by currentDate, field1
0 Karma

limalbert
Path Finder

It still return VALUE and NULL.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

So you're getting column with name as "VALUE" and "NULL"?? Can you post the actual query you're running? (mask anything sensitive)

0 Karma

limalbert
Path Finder

Yes, it return VALUE and NULL, but that's not the only field values. Sorry for the misunderstanding.
Oh, I think I just fix it. The some values in the field contain no data, so it returns NULL and VALUE. I add below.

|search field11=*
| search field12 = "fieldValue1" OR "fieldValue2"
0 Karma

limalbert
Path Finder

So, that only works to exclude NULL, but it doesn't work to exclude VALUE.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

How about this

index=app_fig (keywordA keywordB* keywordC) OR (keywordD keywordE keywordF) OR (keywordG keywordH)
  | rex "2017-(?P<currentDate>\w*.\w*.\w*)\s(?P<currentTime>\d*\:\d*\:\d*)\s\[" 
   | rex "(type|fieldType)\=(?<field11>[^,]*)"
   | rex "fieldName\":\"(?<field12>[^\"]*)"
   | rex "(workOrder|workOrderNumber)=(?<woNum1>\w*)\,"
   | rex "wo:(?<woNum2>\w*)_"  
| search field11=* OR field12=* 
  | eval field1=if(searchmatch("keywordG keywordH"), field12,field11)
  | eval woNum=if(searchmatch("keywordG keywordH"), woNum2,woNum1)
  | chart dc(woNum) by currentDate, field1
0 Karma

limalbert
Path Finder

Issue is fixed. Below is the solution. Thank you! I used the one with coalesce. It's much cleaner

|search field1 != ""
0 Karma

limalbert
Path Finder

It still has VALUE as one of the field value, and the NULL is excluded.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Depends upon the queries of those 3 reports. As long as their filter/search logic can be combined, they can too.

0 Karma

limalbert
Path Finder

Ok. I know that it can be combined depending of the query. I was thinking of combining it by report name, since each reports is created with below. But, the regex for each field aren't unique between reports.
chart dc(woNum) by currentDate, referral

0 Karma

somesoni2
SplunkTrust
SplunkTrust

That can be made same (may be extract using different names and use eval with coalesce to create a common field). You'd get a dead-straight answer if you could share your searches/queries.

0 Karma

limalbert
Path Finder

Sorry for the late update. I posted the 3 reports code in my original question.

0 Karma

woodcock
Esteemed Legend

Yes, it is possible.

sherifhmdy
New Member

how can we do it?

0 Karma

woodcock
Esteemed Legend

give us something with which to work. What are your 3 searches?

0 Karma

limalbert
Path Finder

Are you asking the reports' name? Or searches within report?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...