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
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
hey I got it
Thank you
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
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
The double closing parentheses after match(_raw,"Connecting to DPM"))
should only be a single closing parenthesis.
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
Like I said it was not tested
and maybe you need to adapt it.....so, now it is tested - see my updated answer
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
update ping....