Reporting

Report after comparing two events

runiyal
Path Finder

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!

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust
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.
---
If this reply helps you, an upvote would be appreciated.
0 Karma

runiyal
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

runiyal
Path Finder

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.

index=foo ("Query String:" OR "Upload Completed" AND "SIZE=0.0 KB" )
| rex "empNumber=(?<empNumber>\d+)"
| rex "deptCode=(?<DeptCode>\d+)"
| rex "source=(?<Source>[^,]+)"
| rex "uploadedBy=(?<uploadedBy>[^&,]"
| rex "name=(?<name>[^&,]+)"
| rex "location=(?<location>[^&]+)"
| replace "+" with " " in name
| rex "\\\\(?<FileName>\w+\.?\w+$)"
| stats values(*) as * by empNumber, Source, DeptCode, uploadedBy
| table _time empNumber, Source, DeptCode, uploadedBy, FileName

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust
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".
---
If this reply helps you, an upvote would be appreciated.
0 Karma

to4kawa
Ultra Champion
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.

0 Karma

runiyal
Path Finder

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} 

 

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!