topic Re: Report after comparing two events in Reporting

Report after comparing two events

runiyal — Wed, 15 Jul 2020 16:42:08 GMT

In my logfile there are two separate lines in there, I need to look at first one and get some of the result but then merge with the above event and create a report like in the following example -

****** NEW WEBSCRIPT REQUEST ****** [1st Line]
---
Uploading file to TestDB from \\server1\TestFolder\out\1stTestforme.pdf
---
---
---
Upload Completed /Repo/service/ABC/upload succeeded Request: {empNumber=12345, name=1st Test For Me, uploadedBy=User1, DeptCode=6789, source=App1, mimeType=application/pdf}, Response: {MIMETYPE=application/pdf, SIZE=0.0 KB} [Last Line]

I need to get a report that Provides me a report of all records that that 0KB (Event 2) but File Name should be from the Event 1

So, result Set can be -

empNumber source DeptCode UoloadedBy FileName
12345 App1 6789 User1 1stTestforme.pdf

Will appreciate if anyone can help in this.

Thanks!

Re: Report after comparing two events

richgalloway — Wed, 15 Jul 2020 17:23:26 GMT

Presumably, there are many lines of logs between Event 1 and Event 2. How is Splunk supposed to correlate them? They have no common fields.

Re: Report after comparing two events

runiyal — Wed, 15 Jul 2020 17:39:23 GMT

Thanks Rich.

Yes, there can be lot of lines but this upload goes in one set of batch.

First line starts with - ****** NEW WEBSCRIPT REQUEST ******

and Last line ends with - SIZE=0.0 KB}

Re: Report after comparing two events

runiyal — Wed, 15 Jul 2020 17:55:19 GMT

secondly, there is another row in there which provides the Query String and it has info like -

Query String: empNumber=12345&deptCode=6789&source=App1&contentType=letter&location=D:\testfolder\out\1stTestforme.pdf&name=1st+Test+For+Me&uploadedBy=User1&mimeTYpe=application%2Fpdf

As you can see it has Location and Name.

I need to get Location but in the Query I don't have response which shows 0KB.

So, query first need to read -

Upload Completed /Repo/service/ABC/upload succeeded Request: {empNumber=12345, name=1st Test For Me, uploadedBy=User1, DeptCode=6789, source=App1, mimeType=application/pdf}, Response: {MIMETYPE=application/pdf, SIZE=0.0 KB} [Last Line]

Get all the reset set and then later search the above line (query string) to see what the value there is in the location. and bring it in the report. Query String also has "name" which has been replaced "space" by "+" in Query String.

So, report should get info from these two rows....

------ Upload Complete Line ------ -- Query String --
empNumber source DeptCode UoloadedBy FileName
12345 App1 6789 User1 1stTestforme.pdf

Hope this new info helps.

Re: Report after comparing two events

richgalloway — Wed, 15 Jul 2020 19:17:12 GMT

See if this helps.

index=foo ("Query String:" OR "Upload Completed") ```Extract fields. These rex commands can be removed if the fields are extracted automatically.``` | rex "empNumber=(?<empNumber>\d+)" | rex "deptCode=(?<DeptCode>\d+)" | rex "source=(?<Source>[^,]+)" | rex "uploadedBy=(?<uploadedBy>[^&,]" | rex "name=(?<name>[^&,]+)" | rex "location=(?<location>[^&]+)" ```Keep everything below``` | replace "+" with " " in name | rex "\\(?<FileName>\w+\.?\w+$)" | stats values(*) as * by empNumber, Source, DeptCode, uploadedBy | table empNumber, Source, DeptCode, uploadedBy, FileName

Re: Report after comparing two events

runiyal — Wed, 15 Jul 2020 19:52:35 GMT

Thanks a lot Rich.

Can you confirm "FileName" in report will give me whats in Location 1stTestforme.pdf (D:\testfolder\out\1stTestforme.pdf)

I updated the Query so that it gets only 0KB file and escape was not working so added two back slash.

But what I am seeing is although the search works fine ans shows 12 events -

Complete 12 events (7/15/20 11:44:00.000 AM to 7/15/20 3:44:32.000 PM)

But I don't see any output in report.

Re: Report after comparing two events

to4kawa — Wed, 15 Jul 2020 20:15:36 GMT

index=_internal |head 1 | fields _raw _time | eval _raw="****** NEW WEBSCRIPT REQUEST ****** --- Uploading file to TestDB from \\server1\TestFolder\out\1stTestforme.pdf --- --- --- Upload Completed /Repo/service/ABC/upload succeeded Request: {empNumber=12345, name=1st Test For Me, uploadedBy=User1, DeptCode=6789, source=App1, mimeType=application/pdf}, Response: {MIMETYPE=application/pdf, SIZE=0.0 KB}" | kv | rex max_match=0 "Uploading file .*\\\(?<FileName>\S+)"

We can't see the entire log, so We can't make the query.

Re: Report after comparing two events

richgalloway — Thu, 16 Jul 2020 00:31:02 GMT

Yes, the FileName field is intended to be the file name part of the location field.
The lack of results could be because one of the inputs to the stats command is empty. Try running the query without "stats" and "table".