How to get 3 different outputs in a single column

[Laya123]

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

[Laya123]

hey I got it

Thank you

[Laya123]

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

[MuS · Accepted solution]

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]

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

[Laya123]

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

[MuS]

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

[Laya123]

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

[MuS]

update ping....