Squid parser data connector could not parse the data correctly. #8999

v-jackzou · 2023-09-13T05:57:25Z

Bug description

After installing the squid proxy connector from the Sentinel content hub solution. It will start ingesting the squidProxy_CL log into the workspace. However the parser could not parse the day column correctly.

To reproduce

The squidProxy_CL with the RAW data ingested as follows;

Sep 11 13:27:33 xxxxxx (squid-1): 100000000.547 701 1.1.1.1 TCP_MISS/200 386364 POST http://sample.com :9002/webSvc/Get - HIER_DIRECT/1.1.1.1 application/json

However, after the parsing the day column only parse 11 instead of Sep 11

github-actions · 2023-09-13T05:57:38Z

Thank you for submitting an Issue to the Azure Sentinel GitHub repo! You should expect an initial response to your Issue from the team within 5 business days. Note that this response may be delayed during holiday periods. For urgent, production-affecting issues please raise a support ticket via the Azure Portal.

v-rbajaj · 2023-09-13T11:30:29Z

Hi @v-jackzou , thanks for flagging this issue, we will soon get back to you on this. Thanks!

v-rbajaj · 2023-09-22T14:56:24Z

Hi @v-jackzou, we have reached out to concerned team for this, we will get back to you by 27 Sep 2023.

v-jackzou · 2023-09-26T02:18:43Z

Sure! thanks looking forward to your updates!

v-jackzou · 2023-10-01T06:40:09Z

HI team, just a follow up regarding this issue, any updates regarding this issue? @v-rbajaj

v-jackzou · 2023-10-04T08:15:54Z

HI team

Any updates for this issue ?

v-rbajaj · 2023-10-05T13:38:01Z

Hi @v-jackzou, we investigated on this issue and we could find that the parser was optimized for this issue over this PR
https://github.com/Azure/Azure-Sentinel/pull/7490/files

Please have a look and update the solution if its not updated.

v-rbajaj · 2023-10-10T08:56:25Z

Hi @v-jackzou, Gentle Reminder: We are awaiting for your response on this issue. If you still need to keep this issue active please respond on it in the next 2 days . If we don't receive response, we will be close this issue.

v-jackzou · 2023-10-10T09:04:01Z

Hi @v-rbajaj allow me to sync with the customer

ypendhare · 2023-10-10T10:06:43Z

issue is yet not fixed, morever fields are distorted

v-rbajaj · 2023-10-12T07:43:23Z

Hi @ypendhare, thanks for updating us, let us check and will get back to you by 16 Oct 2023

v-jackzou · 2023-10-27T04:52:46Z

Hi team, any updates for this issue?

v-rbajaj · 2023-11-01T16:00:01Z

Hi @v-jackzou, apologies for delay in response, we will provide you an update by 7 Nov 2023

v-rbajaj · 2023-11-07T09:24:33Z

Hi @v-jackzou, we are still investigating on this issue, we will provide an update by 09 Nov 2023.

v-rbajaj · 2023-11-09T18:19:55Z

Hi @v-jackzou, we have tested the parser and it seems to be parsing the data correctly.

Also can you please try below parser query and see if this helps you to fix the issue?

SquidProxy_CL
| parse kind=regex RawData with * " " EpochTimeExtended " " Duration " " SrcIpAddr " " ResultStatusCode " " Bytes " " HttpRequestMethod " " Url " " Username " " PeerStatusHost " " ContentType
| parse EpochTimeExtended with * "+" EpochTime
| parse ResultStatusCode with ResultCode "/" StatusCode
| parse PeerStatusHost with PeerHost "/" StatusHost
| extend EventEpochTime = tolong(replace(@"\.","",EpochTime))
| extend EventTime = datetime(1970-01-01) + tolong(EventEpochTime/1000) * 1sec
| extend CacheRawLog = extract_all(@"^([0-9\/]+\s[0-9\:]+)\s(\S+)\s([\S\s]+)", dynamic([1,2,3]), RawData)
| mv-expand CacheRawLog
| extend CacheEventTime = todatetime(replace("/","-",tostring(CacheRawLog[0]))),
         Description = tostring(CacheRawLog[2])
| extend EventTime = iif(isempty(EventTime), CacheEventTime, EventTime)
| project-away RawData, EpochTime, EventEpochTime, CacheRawLog, CacheEventTime

ypendhare · 2023-11-10T07:10:12Z

Team, i am still seeing issue, this time a different one - EpochTimeExtended field is mixed with other field data

also let me mention the original source format i am getting as below

if you observe - you will note the starting string till (squid-1) adds full date + time+ source squid server + app name but similar is not pushed to Sentinel table

v-rbajaj · 2023-11-14T14:22:24Z

Hi @ypendhare, can you please share the complete list of raw log messages which you guys are trying to parse, because the ones which we have is parsing the data correctly.
Thanks

v-muuppugund · 2023-11-16T07:38:47Z

Hi @ypendhare Could you please share the complete list of raw log messages so we can parse the data correctly

v-jackzou · 2023-11-20T07:59:04Z

Hi team

Thanks again for the help on this, I have provided the required information to @v-muuppugund , please investigate and we will wait for your response.

Thank you

v-muuppugund · 2023-11-20T11:06:16Z

Hi @v-jackzou ,As discussed over call,will analyse in detail over the data provided and will get back to you by 23Nov23 and will share some updated by tomorrow 5pm over teams call

ypendhare · 2023-11-22T03:16:24Z

squidlog.log
@v-muuppugund attached is file

v-muuppugund · 2023-11-22T12:45:48Z

@ypendhare Thanks for sharing the logs,will check on it and get back to you

v-rbajaj · 2023-11-23T13:20:11Z

We are checking the logs and trying to ingest similar data and will get back to you by 27 Nov 2023.

v-rbajaj · 2023-11-27T12:42:54Z

We are trying to ingest similar data and will get back to you by 30 Nov 2023

v-rbajaj · 2023-11-29T13:58:17Z

We have reached out to concerned team we will get back to you by 1 Dec 2023

v-rbajaj · 2023-12-01T12:47:19Z

Hi @ypendhare, can you please share these logs in csv file?

v-rbajaj · 2023-12-12T11:34:05Z

Hi @ypendhare, Gentle Reminder: We are awaiting for your response on this issue. If you still need to keep this issue active please respond on it in the next 2 days . If we don't receive response, we will be close this issue.

ypendhare · 2023-12-12T13:06:42Z

I have already provided log file refer earlier comments
You can save that as csv as well

v-muuppugund · 2023-12-15T10:27:53Z

Hi @ypendhare ,Apologies for the delayed response,we are working on it and will share update by 21Dec2023

v-muuppugund · 2023-12-20T14:09:29Z

Hi @ypendhare / @v-jackzou ,need some details from customer as we are unable to process the log format to csv and few queries on the issue,Could you please suggest convenient time slots for teams meeting,

v-sudkharat · 2023-12-26T11:42:04Z

Hi @v-jackzou / @ypendhare, Could you please share your convenient time slot for meeting on this mail id - v-muuppugund@microsoft.com

Thanks!

v-muuppugund · 2023-12-28T06:55:08Z

Hi @ypendhare / @v-jackzou ,As discussed on yesterday call ,got the data and the following are the issues and working on it ,will update you

Epoch time is not parsing
Day is not coming properly

v-muuppugund · 2024-01-02T14:38:26Z

Hi @ypendhare / @v-jackzou ,I have done the changes and working on testing,once completed,will update you

v-muuppugund · 2024-01-08T01:23:37Z

Hi @ypendhare / @v-jackzou Still data dump is in progress with large data, Will update you once testing is completed by 10Jan2024

v-muuppugund · 2024-01-17T17:05:42Z

Hi @v-jackzou ,As discussed over teams ,having issues with data, working on it,once done,will test and schedule meeting with customer.

v-muuppugund · 2024-01-21T10:24:30Z

Hi @ypendhare / @v-jackzou ,Completed the testing,scheduled call for the showing the changes on Tuesday i.e. 23/1/24 at 4:30pm to 5pm ,please join the meeting.

v-muuppugund · 2024-01-23T13:58:22Z

Hi @ypendhare ,As discussed over today's call ,Squid proxy parser changes tested successfully and changes are in this branch(https://github.com/Azure/Azure-Sentinel/blob/users/v-muuppugund/SquidProxyParserChanges/Solutions/SquidProxy/Parsers/SquidProxy.txt),as the issue is resolved, we are closing your issue (#8999) . If you still need support for this issue, feel free to re-open at any time. Thank you for your co-operation!

v-amolpatil assigned v-rbajaj and v-sudkharat Sep 13, 2023

v-amolpatil added the Connector Connector specialty review needed label Sep 13, 2023

v-muuppugund added the Parser Parser specialty review needed label Nov 9, 2023

v-sudkharat assigned v-muuppugund and unassigned v-rbajaj Dec 13, 2023

v-muuppugund closed this as completed Jan 23, 2024

v-sudkharat mentioned this issue Apr 22, 2024

SQUID Proxy Parser updates #10330

Closed

Squid parser data connector could not parse the data correctly. #8999

Squid parser data connector could not parse the data correctly. #8999

Comments

v-jackzou commented Sep 13, 2023

github-actions bot commented Sep 13, 2023

v-rbajaj commented Sep 13, 2023

v-rbajaj commented Sep 22, 2023

v-jackzou commented Sep 26, 2023

v-jackzou commented Oct 1, 2023 • edited Loading

v-jackzou commented Oct 4, 2023

v-rbajaj commented Oct 5, 2023

v-rbajaj commented Oct 10, 2023

v-jackzou commented Oct 10, 2023

ypendhare commented Oct 10, 2023

v-rbajaj commented Oct 12, 2023

v-jackzou commented Oct 27, 2023

v-rbajaj commented Nov 1, 2023

v-rbajaj commented Nov 7, 2023

v-rbajaj commented Nov 9, 2023 • edited Loading

ypendhare commented Nov 10, 2023 • edited Loading

v-rbajaj commented Nov 14, 2023

v-muuppugund commented Nov 16, 2023 • edited Loading

v-jackzou commented Nov 20, 2023

v-muuppugund commented Nov 20, 2023

ypendhare commented Nov 22, 2023

v-muuppugund commented Nov 22, 2023 • edited Loading

v-rbajaj commented Nov 23, 2023

v-rbajaj commented Nov 27, 2023

v-rbajaj commented Nov 29, 2023

v-rbajaj commented Dec 1, 2023

v-rbajaj commented Dec 12, 2023

ypendhare commented Dec 12, 2023

v-muuppugund commented Dec 15, 2023 • edited by v-sudkharat Loading

v-muuppugund commented Dec 20, 2023

v-sudkharat commented Dec 26, 2023

v-muuppugund commented Dec 28, 2023

v-muuppugund commented Jan 2, 2024

v-muuppugund commented Jan 8, 2024

v-muuppugund commented Jan 17, 2024

v-muuppugund commented Jan 21, 2024

v-muuppugund commented Jan 23, 2024

v-jackzou commented Oct 1, 2023 •

edited

Loading

v-rbajaj commented Nov 9, 2023 •

edited

Loading

ypendhare commented Nov 10, 2023 •

edited

Loading

v-muuppugund commented Nov 16, 2023 •

edited

Loading

v-muuppugund commented Nov 22, 2023 •

edited

Loading

v-muuppugund commented Dec 15, 2023 •

edited by v-sudkharat

Loading