Splunk Search

How to write a search using my sample test data to get the expected table of results?

Vigneshprasanna
Explorer

Hi Team,

I'm Facing issue in designing a query for the following requirement :

Sample data :

Test data :

2017-08-08 22:38:24,331 **INFO** [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH/NO,RULE.ID:1:1=below minimum value (0) ]
2017-08-08 22:38:24,331 **ERROR**  [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBADRFNO,RULE.ID:1:1=below minimum value (0) ]
2017-08-08 22:38:24,332 **WARN** [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
2017-08-08 22:38:24,333 **INFO** [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
2017-08-08 22:38:24,334 **SEVER** [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]

PARSING THE ABOVE LOG (SAMPLE) :

**Timestamp :** 2017-08-08 22:38:24,331 
**Log_Level :** INFO 
**Connection_factory :** [XYZXYZ] 
**Thread_Number :**(httpXYSGHFA 10.100.1234.12-1234-1234) 
**Application_Message :** 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]

Exported Result

alt text

I tried plenty of formats in the query, please help to design the query to meet the needs.

Regards,
Vigneshprasanna R

0 Karma
1 Solution

niketn
Legend

@Vigneshprasanna try the following run anywhere search which mimics the data as per the question and outputs the result as needed by you. If your challenge is Regular Expression you should try out regex101.com for learning and testing the same.

PS: The commands from | makeresults till first rex command generates the data and fields as per your question ( i have kept same casing for various fields unlike that in the question. So please correct field name casing if required).

| makeresults
| eval data="2017-08-08 22:38:24,331 INFO [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,331 ERROR [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBADRFNO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,332 WARN [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,333 INFO [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,334 SEVER [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "^(?<Timestamp>[^,]+,[^\s]+)\s(?<Log_Level>[^\s]+)\s(?<Connection_Factory>[^\s]+)\s(?<Thread_Number>[^\)]+\))\s(?<Application_Message>.*\])$"
| rex field=Application_Message "APP Response\s\[\s[^\/]+(?<Expected_Log_Level>\/\/-1\/)"
| eval Expected_Log_Level=if(isnull(Expected_Log_Level),Log_Level,"ERROR") 
| table Timestamp Log_Level Connection_Factory Thread_Number Application_Message Expected_Log_Level
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@Vigneshprasanna try the following run anywhere search which mimics the data as per the question and outputs the result as needed by you. If your challenge is Regular Expression you should try out regex101.com for learning and testing the same.

PS: The commands from | makeresults till first rex command generates the data and fields as per your question ( i have kept same casing for various fields unlike that in the question. So please correct field name casing if required).

| makeresults
| eval data="2017-08-08 22:38:24,331 INFO [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,331 ERROR [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBADRFNO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,332 WARN [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,333 INFO [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ];2017-08-08 22:38:24,334 SEVER [XYZXYZ] (httpXYSGHFA 10.100.1234.12-1234-1234) 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_123SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "^(?<Timestamp>[^,]+,[^\s]+)\s(?<Log_Level>[^\s]+)\s(?<Connection_Factory>[^\s]+)\s(?<Thread_Number>[^\)]+\))\s(?<Application_Message>.*\])$"
| rex field=Application_Message "APP Response\s\[\s[^\/]+(?<Expected_Log_Level>\/\/-1\/)"
| eval Expected_Log_Level=if(isnull(Expected_Log_Level),Log_Level,"ERROR") 
| table Timestamp Log_Level Connection_Factory Thread_Number Application_Message Expected_Log_Level
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

niketn
Legend

@Vigneshprasanna, if your issue is resolved please accept the answer to mark this question as answered.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Vigneshprasanna
Explorer

Hi Niketnilay,

Do you have any idea on the issue " https://answers.splunk.com/answers/663602/splunk-query-5.html " this is related to the same log type .

0 Karma

Vigneshprasanna
Explorer

Sorry for the delay and thanks a lot Niketnilay 🙂 your response helped me a lot 🙂

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...