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

[Vigneshprasanna]

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

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

Regards,
Vigneshprasanna R

[niketn · Accepted solution]

@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

[niketn]

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

[Vigneshprasanna]

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 .

[Vigneshprasanna]

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