Splunk Search

How do I combine my results for two searches to display on a bar graph for comparison?

vrmandadi
Builder

Hello,

I am trying to join two searches
1)which gives the count for the last three months and
2)which gives the count for the latest month

index=*d*  source="sample_data"  earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?\w+)_"| stats count by MESSAGE_TYPE | rename count as last3 | append [search index=*d* source="sample_data"  earliest=-1mon@mon latest=now | rex field=MESSAGE_QUALIFIER "(?\w+)_" | stats count by MESSAGE_TYPE | rename count as last1]

Current output:

MESSAGE_TYPE   last3   last1
CMO            115   
CMP            75    
INQ            403   
NIT            23    
REV            23    
CMP                   15 

Expected output:

MESSAGE_TYPE   last3   last1
CMO            115   
CMP            75    15
INQ            403   
NIT                    23    
REV                    23   

The expected output when selecting a bar graph should give two bars for CMP side by side, thus making it easy for comparison, right?
alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give these a try

Updated
Recommended

index=*d* source="_sample_data" earliest=-3mon@mon latest=now 
| eval Period=if( _time<relative_time(now(),"-1mon@mon") ,"last1","last3") 
| rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_"  | chart count over MESSAGE_TYPE by Period 

Option 2

index=*d* source="_sample_data" earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last3 by MESSAGE_TYPE | append [searchindex=*d* source="_sample_data"  earliest=-1mon@mon latest=now | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last1 by MESSAGE_TYPE ]
| stats values(*) as * by MESSAGE_TYPE

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give these a try

Updated
Recommended

index=*d* source="_sample_data" earliest=-3mon@mon latest=now 
| eval Period=if( _time<relative_time(now(),"-1mon@mon") ,"last1","last3") 
| rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_"  | chart count over MESSAGE_TYPE by Period 

Option 2

index=*d* source="_sample_data" earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last3 by MESSAGE_TYPE | append [searchindex=*d* source="_sample_data"  earliest=-1mon@mon latest=now | rex field=MESSAGE_QUALIFIER "(?<MESSAGE_TYPE>\w+)_" | stats count as last1 by MESSAGE_TYPE ]
| stats values(*) as * by MESSAGE_TYPE

vrmandadi
Builder

Hello Somesh,

I tried both the queries both the queries gives only the cmp and its count ,but it does not get the results as attached in the screen shot

Sorry I just updated the question both the searches have same sources,and sourcetype

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Is the rex also same for both the searches OR they differ?? (field=MESSAGE_QUALIFIER to be used in both OR one to use field=MESSAGE_QUALIFIER and other to use field=EWS_MESSAGE_QUALIFIER

0 Karma

vrmandadi
Builder

o yes it is same for both of them..once again sorry

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Well that makes it easy. Try the updated answer.

0 Karma

vrmandadi
Builder

Thanks a lot Somesh,just one more question.

what if I want to do the same for a day in a week and compare to the same day in the previous week like monday of this week to the monday of the previous week

Thanks a ton

0 Karma

vrmandadi
Builder

can You explain what the below command does

| eval Period=if( _time

0 Karma

jmallorquin
Builder

Hi,

A easy way to do that is just sum 7,884e+6 seconds to all the events in the query -3m just to bring the events to the same time of the other query.

Then with time chart you will be able to compare the 3 month with the last month.

Hope i help you.

0 Karma

jmallorquin
Builder

Hi,
No problem try this:

index=d source="sample_data" earliest=-3mon@mon latest=-1@mon | rex field=MESSAGE_QUALIFIER "(?\w+)"  | eval _time=_time+7884000 | eval last="Last 3 month" | append [search index=d source="sample_data_for_splunk_dashboard.json" earliest=-1mon@mon latest=now | rex field=EWS_MESSAGE_QUALIFIER "(?\w+)_"  | eval last="Last month"] | timechart count by last

Hope i help you

0 Karma

vrmandadi
Builder

I tried but it is not giving the results as expected in the attached image

0 Karma

vrmandadi
Builder

I am very new to splunk and I did not understand your answer

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 ...