Splunk Search

How to create the Splunk query Search?

uagraw01
Builder

Hello Splunkers!!

I have mentioned below query and from the below query I want a results as shown below in the excel. Please help me achieve that result.

index=ABC sourcetype=ABC
| eval date_year=strftime('_time',"%Y"), date_month=strftime('_time',"%B"), day_week=strftime('_time',"%A"), date_mday=strftime('_time',"%d"), date_hour=strftime('_time',"%H"), date_minute=strftime('_time',"%M") | stats count count(eval(ShuttleId)) as total by sourcetype
| table sourcetype total
| join max=0 type=outer sourcetype
[| search index=ABC sourcetype=ABC
| eval date_year=strftime('_time',"%Y"), date_month=strftime('_time',"%B"), day_week=strftime('_time',"%A"), date_mday=strftime('_time',"%d"), date_hour=strftime('_time',"%H"), date_minute=strftime('_time',"%M") | stats count by ShuttleId sourcetype _time]
| table ShuttleId count total
| eval condition =if(round((count/total),2) <=0, "GREEN", "RED")
| eval Status =round((count/total),2)
| eval Shuttle_percentage = round(((count/total)*100),2)
| table ShuttleId Shuttle_percentage

 

_time ShuttleId Total_Orders Errors
2022-08-03T00:00:00.000+0000 Shuttle_001 69341 117
2022-08-04T00:00:00.000+0000 Shuttle_002 85640 51
2022-08-05T00:00:00.000+0000 Shuttle_003 72260 43
2022-08-06T00:00:00.000+0000 Shuttle_004 60291 22
2022-08-07T00:00:00.000+0000 Shuttle_005 0 0

 

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| stats count by _time shuttleId
| eventstats sum(count) as total by shuttleId

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

You have present a seemingly random set of SPL which bear little resemblance to your expected output, with meaningless evaluations which are often not used.

What is it you are actually trying to do, and what have you actually tried so far?

0 Karma

uagraw01
Builder

@ITWhisperer  | eval command for condition we can ignore. 

I need a result like :

column 1 : time

column 2 : shuttle name

column 3 : total number of shuttle

column 4 : number of count of each shuttle.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What it the difference between column 3 and 4?

0 Karma

uagraw01
Builder

@ITWhisperer  Column 3 : total count of all the shuttle.  & column 4 : (count of each shuttle/ total count of all the shuttle)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats count by _time shuttleId
| eventstats sum(count) as total by shuttleId

uagraw01
Builder

@ITWhisperer  I think I am getting below results. Please correct me for the below SPL

 

uagraw01_0-1679314570849.png

=========================================================

index=ora_adam sourcetype=OtShuttleError
| eval date_year=strftime('_time',"%Y"), date_month=strftime('_time',"%B"), day_week=strftime('_time',"%A"), date_mday=strftime('_time',"%d"), date_hour=strftime('_time',"%H"), date_minute=strftime('_time',"%M") | stats count count(eval(ShuttleId)) as total by sourcetype _time
| table sourcetype total _time
| join max=0 type=outer sourcetype
[| search index=ora_adam sourcetype=OtShuttleError
| eval date_year=strftime('_time',"%Y"), date_month=strftime('_time',"%B"), day_week=strftime('_time',"%A"), date_mday=strftime('_time',"%d"), date_hour=strftime('_time',"%H"), date_minute=strftime('_time',"%M") | stats count by ShuttleId sourcetype _time]
| eventstats sum(count) as total by shuttleId

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You might want to consider using the bin command to group your counts by hours or days, but it depends on what your requirement actually is.

0 Karma

uagraw01
Builder

@ITWhisperer Let me check and try

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