Splunk Search

How to get 3 different outputs in a single column

Laya123
Communicator

Hi,

I want 3 different outputs in a single column.

I will explain what exactly I want to do

I have activated a project in live, i want to know the whether the project is activated or not to get this output i have used the following query and I am getting result but the project status is coming in 3 different columns instead of that i want output in a single column with project status.

Query:

index=ibm sourcetype=act host=KO*MRI65* OR host=KO*MRI75* | rex field=_raw ".*.*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),.*" | rex field=_raw ".*ERROR: (?.*)"  | eval Tier=if(match(host,"KO...MRI65."), "Launch", "Publish") | transaction activationID maxevents=10000 startswith="RemoteActivateServer START"  | stats eval(match(_raw,"Connecting to DPM")) as Queued, eval(match(_raw,"Project .* activated")) as Activated, eval(match(_raw, "Activate failed")) as Failed by activationID  

after using the above query I am getting results like

activationID         InQueue           Activate               Failed           
ABCDEFF                    0                1                   0
cdtskl                     1                0                   0
ugsjkc                     0                1                   0
jktful                     0                0                   1

I want your help in, is it possible to put these three columns in one columns as 'ProjectStatus' instead of 3 columns

I want out put like;

activationID ProjectStatus

ABCDEFF Activate

cdtskl Queue

ugsjkc Activate

jktful Failed

Please help me to do this

Thanks & Regards

Tags (2)
0 Karma
1 Solution

MuS
Legend

Hi Laya123,

try something like this:

index=ibm sourcetype=act host=KO*MRI65* OR host=KO*MRI75* 
| rex field=_raw ".*.*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),.*" | rex field=_raw ".*ERROR: (?.*)"  
| eval Tier=if(match(host,"KO...MRI65."), "Launch", "Publish") 
| transaction activationID maxevents=10000 startswith="RemoteActivateServer START"  
| eval ProjectStatus=case(match(_raw,"Connecting to DPM") , "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed")
| table activationID ProjectStatus

This was not tested - now it is. Still, maybe you need to adapt it to your needs; but it should get you started ...

cheers, MuS

View solution in original post

Laya123
Communicator

hey I got it

Thank you

0 Karma

Laya123
Communicator

Hi,

Thank you so much for your response it is working,

I need some more help in the same query. I am trying to get some other results along with that output, so I have added few lines, to that query which you shared but I am getting error - Error in 'eval' command: The operator at ', values(ErrorMsg) as Error, values(duration) as Duration, latest(_time) as LastActivation, values(Project) as Project, values(Tier) as Tier' is invalid.

Query -

index=ibm sourcetype=act host=A*MRI65* OR host=A*MRI75* | rex field=_raw "..*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),." | rex field=_raw ".ERROR: (?.)" | eval Tier=if(match(host,"A...MRI65."), "L", "P") | transaction activationID maxevents=10000 startswith="RemoteActivateServer START", | eval ProjectStatus=case(match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed", 1=1, "QUEUE")*, values(ErrorMsg) as Error, values(duration) as Duration, latest(_time) as LastActivation, values(Project) as Project, values(Tier) as Tier | table activationID ProjectStatus | sort LastActivation desc | eval Duration = round(Duration) | eval Queued=Queued-Activated-Failed | rename Duration to "Duration in Seconds" | eval LastActivation = strftime(LastActivation,"%A %b %d %I:%M %P") | rename LastActivation to "Last Activation" | rename Queued to "In Queue / Processing"*

Herewith I am enclosing my previous query and output which i got,

Query

index=ibm sourcetype=act host=A*MRI65* OR host=A*MRI75* | rex field=_raw "..*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),." | rex field=_raw ".ERROR: (?.)" | eval Tier=if(match(host,"A...MRI65."), "L", "P") | transaction activationID maxevents=10000 startswith="RemoteActivateServer START" | stats count(eval(match(_raw,"Connecting to DPM"))) as Queued, count(eval(match(_raw,"Project .* activated"))) as Activated, count(eval(match(_raw, "Activate failed"))) as Failed, values(ErrorMsg) as Error, values(duration) as Duration, latest(_time) as LastActivation, values(Project) as Project, values(Tier) as Tier by activationID | sort LastActivation desc | eval Duration = round(Duration) | eval Queued=Queued-Activated-Failed | rename Duration to "Duration in Seconds" | eval LastActivation = strftime(LastActivation,"%A %b %d %I:%M %P") | rename LastActivation to "Last Activation" | rename Queued to "In Queue / Processing"

the output is coming like this

Output

activationID InQueue Activate Failed Error Druation in Sec Last activation Project Tier

ABCDEFF 0 1 0 2 24thSep abc L
cdtskl 1 0 0 5 20thoct edf P
ugsjkc 0 1 0
jktful 0 0 1

but i want project status which is bold in the output in one column as i said yesterday along with other results

Can you help me in this

Thanks & Regards

0 Karma

MuS
Legend

Hi Laya123,

try something like this:

index=ibm sourcetype=act host=KO*MRI65* OR host=KO*MRI75* 
| rex field=_raw ".*.*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),.*" | rex field=_raw ".*ERROR: (?.*)"  
| eval Tier=if(match(host,"KO...MRI65."), "Launch", "Publish") 
| transaction activationID maxevents=10000 startswith="RemoteActivateServer START"  
| eval ProjectStatus=case(match(_raw,"Connecting to DPM") , "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed")
| table activationID ProjectStatus

This was not tested - now it is. Still, maybe you need to adapt it to your needs; but it should get you started ...

cheers, MuS

martin_mueller
SplunkTrust
SplunkTrust

The double closing parentheses after match(_raw,"Connecting to DPM")) should only be a single closing parenthesis.

Laya123
Communicator

Hi,

Thank you so much for your immediate response,

But after executing that query I am getting this error

Error in 'eval' command: The operator at ', "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed")' is invalid.

Query is: index=ibm sourcetype=act host=KO*MRI65 OR host=KO*MRI75* | rex field=_raw "..*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),." | rex field=_raw ".ERROR: (?.)" | eval Tier=if(match(host,"KO...MRI65."), "Launch", "Publish") | transaction activationID maxevents=10000 startswith="RemoteActivateServer START" | eval ProjectStatus=case(match(_raw,"Connecting to DPM")) , "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed") | stats ProjectStatus by activationID*

Please help me

Thanks & Regards

0 Karma

MuS
Legend

Like I said it was not tested and maybe you need to adapt it.....so, now it is tested - see my updated answer

0 Karma

Laya123
Communicator

Hi,

Thank you so much for your immediate response,

But after executing that query I am getting this error

Error in 'eval' command: The operator at ', "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed")' is invalid.

Query is: index=ibm sourcetype=act host=KO*MRI65 OR host=KO*MRI75* | rex field=_raw "..*ACT,(?[0-9]+ - [0-9|a-z|A-Z]+),." | rex field=_raw ".ERROR: (?.)" | eval Tier=if(match(host,"KO...MRI65."), "Launch", "Publish") | transaction activationID maxevents=10000 startswith="RemoteActivateServer START" | eval ProjectStatus=case(match(_raw,"Connecting to DPM")) , "Queued" , match(_raw,"Project .* activated"), "Activated" , match(_raw, "Activate failed"), "Failed") | stats ProjectStatus by activationID*

Please help me

Thanks & Regards

0 Karma

MuS
Legend

update ping....

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...