Splunk Query

[Vigneshprasanna]

Hi Team,

I have a challenge here, i totally have no idea to design this query, Please help in doing this.

Test Data :

Line Number 1 => 2017-08-08 22:38:24,331 INFO XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Request [ ID_01SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 2 => 2017-08-08 22:39:45,331 WARN XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Request [ ID_02SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 3 => 2017-08-08 22:42:57,331 ERROR XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] [ ID_34SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 4 => 2017-08-08 22:48:24,331 SEVIER XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] [ ID_23SDFBH//1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 5 => 2017-08-08 22:55:23,331 INFO XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] [ //-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 6 => 2017-08-08 22:58:32,331 INFO XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Request [ ID_32SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 7 => 2017-08-08 23:01:42,331 ERROR XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Request [ ID_01SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 8 => 2017-08-08 22:38:34,331 INFO XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Request [ ID_02SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]
Line Number 9 => 2017-08-08 22:38:25,331 INFO XYZXYZ 22:38:24,331 INFO [APP_INVOKE_MSG] APP Response [ ID_23SDFBH//-1/NO,RULE.ID:1:1=below minimum value (0) ]

Data Parsing Formate:

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

In the Above set of test data, i wanna write a query in the way it should meet the following needs

Case 1:

If we are searching for the CUS_ID = ID_01SDFBH (This id will be in the 1st line of the test data in the Application_Message field)
Splunk should take the the thread number (here it should take "httpXYSGHFA 10.100.1234.12-1234-81" & "httpXYSGHFA 10.100.1234.12-1234-33")
Example Using Test Data
Here the splink should display the Thread Number's in line Number 1 & 7 because as the log line contain the Search test which is CUS_ID = ID_01SDFBH

Case 2:
When I choose the thread ID "httpXYSGHFA 10.100.1234.12-1234-81" splunk should return the log lines between the next occurrence of the (Same Thread Number)"httpXYSGHFA 10.100.1234.12-1234-81" & String "APP Request"

Example Using Test Data
Here the splunk should display from the Line 1 to Line 5 as the line 1 has the CUS_ID = ID_01SDFBH + "APP Request" + Thread Number="httpXYSGHFA 10.100.1234.12-1234-81", And ends at the line 5 as the line 6 has Same Threads Number "httpXYSGHFA 10.100.1234.12-1234-81" & "APP Request"

[arihant16cse]

....| rex field=_raw "(?\d+-\d+-\d+\s+\d+:\d+).[xyzxyz]\s+((?.))\s+.*"

[Richfez]

So I started working through extractions (for reference included below) but quickly realized I don't understand the scope of this question.

1) Do you have extractions for the fields you are trying to use already?
2) Where did that "Test data" come from - that looks like a spreadsheet so it was manually done?
3) And what's the flow here? It seems this is really a problem with dashboard drill-downs, is that right?

If number 3 is really the issue, well, we have to walk before we run by defining fields and getting searches that make happen what you want to have happen. After you get that sorted out, then dashboard building is just linking those various pieces we've created together and is relatively simple.

But you didn't tell us how to extract fields from the "Application Message". Do you think the only field you need in there is the CUS_ID, and CUS_ID is the test between that one [ and the following /, does that sound right? And for completeness sake, Application_Message appears to be regular enough we can likely just extract all the pieces easily enough, so if you have a definition for those fields that you'd like to use and could pass that along, I think that would be useful.

Your case 2: probably just a group by clause, but I think first off we need to straight out field extractions and the various searches we'll need for case 1, then I think case 2 may be pretty simple. (See comments about not getting ahead of ourselves).

I think this is all not terribly difficult to do, but there's a lot of pieces that seem all tangled up together. Could you clarify those questions above?

... | rex ",(?<Logging_Priority>\d+)\s+(?<Log_Level>\w+)\s+\[(?<Connection_factory>[^]]*)\]\s\((?<thread_id>[^)]*)\)(?<Application_Message>.*)"

[Vigneshprasanna]

Hi Rich,

1. Im pulling in the log data from an application log folder, and have already extracted in the above given "Data Parsing Formate: " For better understanding im providing a log and its regx

Log :
This shows you one request and response

2018-05-14 14:00:00,204 INFO STDOUT 14:00:00,204 [domain:name:application:invoke] INFO - APP Request [ app.ico.SELECT,,***********//TN599qerqi839,RT1.call.ads,@ID:EQ=TN599qerqi839 ]

2018-05-14 14:00:00,236 INFO STDOUT 14:00:00,236 [domain:name:application:invoke] INFO - APP Response [ ,," 20171214" ]

Regax :
^(?P[^,]+)[^,\n]*,(?P\d+)\s+(?P\w+)\s+(?P[^ ]+)\s+(?P[^ ]+)\s+(?P.+)

1. The data is not coming from a spreed sheet its from a .log file we generally open it in note pad.
2. Yes, its a folw from a dashboard to drill down.

Application MSG is a field that is not defined properly that contains the data generated by the application , as we are not maintaining the session id we are not able to tract the request and response of a specific request so the idea is that the thread number generated at the time of request will flow until the response or error is thrown, using this i know the value customer ID i will use it to get the logs having the customer id and "- APP Request "
for example if i want session logs of id 1445

i will search for index=* Application_MSG ="1445" and " - APP Request"

so the above query will list me the logs contains index=* Application_MSG ="1445" and " - APP Request"
from the logs i need to take the thread ID and have a drop down

in that drop down if i select an thread number (for example if im selecting : http-Applicationserver%10.10.10.100.100-8080-5) it should return all the logs between this thread number and the next occurrence of the same thread number thread number & " - APP Request"(for the above example it will be like i should list the logs in between the until i get the next log having the same thread number "http-Applicationserver%10.10.10.100.100-8080-5" & "- APP Request " )

to term it simple i should list all the logs in between log congaing thread number "http-Applicationserver%10.10.10.100.100-8080-5 " & "- APP Request" to "http-Applicationserver%10.10.10.100.100-8080-5 " & "- APP Request"

for selecting the thread number "http-Applicationserver%10.10.10.100.100-8080-5" only we are using some ID in the application MSG "1445"(this can vary)

[cpetterborg]

From what I have tried, you won't be able to go from a dashboard to a separate drill down page, but you can use the drill down to change a token in the dashboard to allow you to make another dashboard panel use that data to perform it's search and produce the results you would have wanted in the separate drill down page.

One complication is that you only want to go from the first occurrence of the Thread_Number to the next event with the same Thread_Number AND "- APP Request". It might be possible with a transaction. But then you lose the field extractions for making a clean table of data. It is possible to combine the fields, then separate them again, but if that can be avoided, it would be easier.

[Vigneshprasanna]

Hi cpetterborg ,

Hope the below info can help us to understand

for the below sample data

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet
2018-05-14 14:25:00,171 INFO STDOUT 14:25:00,171 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ ,," 123145353" ]
2018-05-14 14:25:00,296 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.
2018-05-14 14:25:00,311 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.

and the above sample data is parses as
Fields
AUDIT_TIME,LOGGING_PRIORITY,LOG_LEVEL,THREAD_NUMBER ,CONNECTION_FACTOR,AUDIT_DATA
Regular Expression
^(?P[^,]+)[^,\n]*,(?P\d+)\s+(?P\w+)\s+(?P[^ ]+)\s+(?P[^ ]+)\s+(?P.+)

In the above data i have mapped THREADNUMBER :"http-xxxxxx%xx.123.123.123-800-8" As value A and AUDIT_DATA: "APP Request" as value B

so now im trying to print the below events as they are the values between the same values combinationa of A & B

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet

[cpetterborg]

Is this the sort of thing that would do what you want:

[Vigneshprasanna]

Hi cpetterborg,
No I’m not looking for all text ☹️.

[cpetterborg]

Okay. Good luck then. I don't know where to go with this at this point. Perhaps someone else will have some idea of where to go with an answer.

[Vigneshprasanna]

Hi @cpetterborg

the above i have provided was jest a sample info, for better understanding i have added some details below hope this will help us .

or the below sample data

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet
2018-05-14 14:25:00,171 INFO STDOUT 14:25:00,171 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ ,," 123145353" ]
2018-05-14 14:25:00,296 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.
2018-05-14 14:25:00,311 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.

and the above sample data is parses as
Fields
AUDIT_TIME,LOGGING_PRIORITY,LOG_LEVEL,THREAD_NUMBER ,CONNECTION_FACTOR,AUDIT_DATA
Regular Expression
^(?P[^,]+),(?P\d+)\s+(?P\w+)\s+(?P[^ ]+)\s+(?P[^ ]+)\s(?P.+)

In the above data i have mapped THREADNUMBER :"http-xxxxxx%xx.123.123.123-800-8" As value A and AUDIT_DATA: "APP Request" as value B

so now im trying to print the below events as they are the values between the same values combinationa of A & B

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet

Regards,
Vigneshprasanna R

[cpetterborg]

The Regax that you used above (Regex) doesn't have the names in it. Can you provide the data again, but this time use the "code" button (101010) to format the data properly to give all the information. There may be some other data that is missing that we don't even know about that may be in this same situation. This will aid us in answering your question.

[Vigneshprasanna]

@rich7177 do you have any idea on this ??

[Richfez]

Are any fields being extracted in this or do we need to start there?