Adding fields to search results

[poonama]

Hello,

I have many stacktraces including keywords like "stackoverflow", "deadlock","Database connection closed". I want to search these errors and display time, host, sourcetype, source, the error message and the count of the error appeared. I have acheived this but partially. Below is my search statement. Can anyone help me in this.

index=websphere | eval test_msg=case(match(_raw,"The connection to the database is closed"),"The connection to the database is closed",match(_raw,"SQLEXCEPTION"),"SQLEXCEPTION") | stats count by test_msg

[gcusello · Accepted solution]

Hi poonama,
let me understand: do you want to add to your search results also time, host, sourcetype, source, etc... or what?
if you want to add these additional fields to your stats command in aggregated format, you have to add values(fields) AS field for each field you want.
In other words:

index=websphere 
| eval test_msg=case(match(_raw,"The connection to the database is closed"),"The connection to the database is closed",match(_raw,"SQLEXCEPTION"),"SQLEXCEPTION") 
| stats values(_time) AS _time values(host) AS host values(sourcetype) AS sourcetype values(source) AS source count by test_msg

Bye.
Giuseppe

[poonama]

Thank you Giuseppe.. It worked fine apart from one thing. Its not showing proper time, i.e it should show the time of the event on which it occurred. But its showing 1/1/01 12:00:00.000 AM. Do you know why its showing like this? Ideally _time should give the correct results.

[gcusello]

Hi,
"1/1/01 12:00:00.000 AM" it's an example or you really have these time?
if you have this strange value, try to reduce time period and check if it's still present.
If you have more _time values, you can choose to take the first or the last using earliest or latest instead values.
Bye.
Giuseppe

[gcusello]

Male a try: Maybe epochtime is in milliseconds, try to divide _time For 1000 and then convert result using strfti.e.
Bye.
Giuseppe

[poonama]

I am really getting this 1/1/01 12:00:00.000 AM. I dont know why this time is coming. This time span is not even there in search list. I have tried to reduce time period, its still showing that only.
How can I use first or the last using earliest or latest? Can you please give an example