topic Re: Splunk query Search in Splunk Search

How to create the Splunk query Search?

uagraw01 — Mon, 20 Mar 2023 21:56:57 GMT

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

Re: Splunk query Search

ITWhisperer — Mon, 20 Mar 2023 11:32:15 GMT

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?

Re: Splunk query Search

uagraw01 — Mon, 20 Mar 2023 11:35:34 GMT

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

Re: Splunk query Search

ITWhisperer — Mon, 20 Mar 2023 11:37:52 GMT

What it the difference between column 3 and 4?

Re: Splunk query Search

uagraw01 — Mon, 20 Mar 2023 11:40:18 GMT

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

Re: Splunk query Search

ITWhisperer — Mon, 20 Mar 2023 11:42:55 GMT

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

Re: Splunk query Search

uagraw01 — Mon, 20 Mar 2023 11:44:02 GMT

@ITWhisperer Let me check and try

Re: Splunk query Search

uagraw01 — Mon, 20 Mar 2023 12:16:39 GMT

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

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

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

Re: Splunk query Search

ITWhisperer — Mon, 20 Mar 2023 14:41:31 GMT

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.