Data Collector API and TimeGenerated property.

%3CLINGO-SUB%20id%3D%22lingo-sub-3178862%22%20slang%3D%22en-US%22%3EData%20Collector%20API%20and%20TimeGenerated%20property.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3178862%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'm%20trrying%20to%20ingest%20some%20logs%20in%20to%20a%20Sentinel%20workspace%20but%20I'm%20having%20issues%20with%20populating%20the%20correct%20TimeGenerated%20property.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAccording%20to%20the%20ol'%20docs%3A%3C%2FP%3E%3CP%3Etime-generated-field%20is%20the%20name%20of%20a%20field%20in%20the%20data%20that%20contains%20the%20timestamp%20of%20the%20data%20item.%20If%20you%20specify%20a%20field%2C%20its%20contents%20are%20used%20for%20TimeGenerated.%20If%20you%20don't%20specify%20this%20field%2C%20the%20default%20for%20TimeGenerated%20is%20the%20time%20that%20the%20message%20is%20ingested.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EWe%20also%20know%20from%20that%20page%20that%20that%20contents%20of%20the%20message%20field%20should%20follow%20the%20ISO%208601%20format%20YYYY-MM-DDThh%3Amm%3AssZ.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%2C%20doing%20a%20cheeky%20copy%20paste%2C%20I've%20setup%20a%20task%20in%20a%20LogicApp%20to%20do%20just%20that%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sirkillnotalot_2-1645108685720.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349074iE1EF2BF60E8CB25A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sirkillnotalot_2-1645108685720.png%22%20alt%3D%22sirkillnotalot_2-1645108685720.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20the%20property%20was%20formatted%20and%20sent%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sirkillnotalot_1-1645108624000.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349072i9532814992BA1F4D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sirkillnotalot_1-1645108624000.png%22%20alt%3D%22sirkillnotalot_1-1645108624000.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20confirmed%20in%20the%20rawOutput%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%0A%22method%22%3A%20%22post%22%2C%0A%22headers%22%3A%20%7B%0A%22Log-Type%22%3A%20%22Example%22%2C%0A%22time-generated-field%22%3A%20%222022-02-17T11%3A25%3A40Z%22%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20when%20querying%20the%20table%20the%20TimeGenerated%20is%20reflecting%20the%20Ingestion%20time%20only.%20To%20confirm%20this%20I%20added%20a%20couple%20of%20properties%20to%20the%20JSON%20payload%20to%20include%20utcNow()%20as%20RunTime_t%20and%20the%20original%20event%20time%20as%20TimeStamp_s%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sirkillnotalot_0-1645108818915.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349075i30E27C3ECFEA27D3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sirkillnotalot_0-1645108818915.png%22%20alt%3D%22sirkillnotalot_0-1645108818915.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20work%20around%20it%20in%20the%20short-term%20by%20using%20a%20function%20to%20do%20something%20like%20the%20below.%20But%20would%20much%20rather%20preserve%20the%20original%20logs'%20TimeStamp.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EExample%0A%7C%20extend%20TimeGenerated%20%3D%20TimeStamp_s%0A%7C%20where%20TimeGenerated%20%26lt%3B%3D%20ago(1h)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHave%20I%20missed%20something%20_really_%20obvious%20here%20or%20this%20simply%20not%20working%20as%20intended%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReferences%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flogs%2Fdata-collector-api%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Monitor%20HTTP%20Data%20Collector%20API%20-%20Azure%20Monitor%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureloganalyticsdatacollector%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Log%20Analytics%20Data%20Collector%20-%20Connectors%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdits%3A%20Added%20screenshot%2C%20formatting%2C%20references%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3183737%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Collector%20API%20and%20TimeGenerated%20property.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F851041%22%20target%3D%22_blank%22%3E%40sirkillnotalot%3C%2FA%3E%26nbsp%3BLooking%20at%20this%20documentation%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureloganalyticsdatacollector%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Log%20Analytics%20Data%20Collector%20-%20Connectors%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3B%20it%20states%20that%20the%20Time-generated-field%20%22Represents%20the%20original%20date%20and%20time%20of%20the%20record.%22%20but%20it%20doesn't%20actually%20state%20that%20this%20is%20the%20TimeGenerated%20field%20(although%20it%20does%20lead%20one%20to%20think%20it%20would%20be).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20adding%20any%20of%20the%20other%20date%2Ftime%20related%20fields%2C%20was%20there%20any%20other%20field%20that%20had%20a%20date%2Ftime%20in%20it%3F%26nbsp%3B%20%26nbsp%3BMaybe%20something%20like%20TimeGenerated_t%20or%20TimeStamp_t%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3183974%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Collector%20API%20and%20TimeGenerated%20property.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20fields%20that%20are%20date%2Ftime%20was%20the%20TimeGenerated%20passed%20in%20the%20original%20payload.%20As%20a%20bit%20of%20a%20test%20I%20stripped%20that%20field%20out%20using%20removeProperty(items('For_each')%2C'TimeGenerated')%20and%20used%20a%20static%20entry%20in%20the%20time-generated-field%20field%20instead%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sirkillnotalot_0-1645188926704.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349463iF42FBD71FC3BDD91%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sirkillnotalot_0-1645188926704.png%22%20alt%3D%22sirkillnotalot_0-1645188926704.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ebut%20all%20I%20get%20is%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sirkillnotalot_2-1645188998821.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349466i072201BE7595D590%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sirkillnotalot_2-1645188998821.png%22%20alt%3D%22sirkillnotalot_2-1645188998821.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20initially%20thought%20the%20same%20as%20you%20and%20that%20the%20documentation%20may%20be%20misleading%20or%20incorrect%20but%20I've%20had%20a%20response%20from%20an%20MS%20support%20rep%20who've%20confirmed%20that%20the%20field%20is%20intended%20to%20preserve%20the%20record%20time%20stamp%2C%20not%20the%20ingested%20timestamp.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThink%20I%20might%20go%20create%20a%20new%20basic%20LogicApp%20and%20see%20if%20I%20can%20reproduce%20using%20a%20payload%20I%20control%20via%20PostMan%20and%20see%20if%20it's%20something%20in%20the%20way%20it%20processes%20records%20in%20a%20for_each%20loop.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi guys,


I'm trrying to ingest some logs in to a Sentinel workspace but I'm having issues with populating the correct TimeGenerated property.


According to the ol' docs:

time-generated-field is the name of a field in the data that contains the timestamp of the data item. If you specify a field, its contents are used for TimeGenerated. If you don't specify this field, the default for TimeGenerated is the time that the message is ingested.


We also know from that page that that contents of the message field should follow the ISO 8601 format YYYY-MM-DDThh:mm:ssZ.


So, doing a cheeky copy paste, I've setup a task in a LogicApp to do just that:

sirkillnotalot_2-1645108685720.png

 

I can see the property was formatted and sent:

sirkillnotalot_1-1645108624000.png

 

and confirmed in the rawOutput:

 

{
"method": "post",
"headers": {
"Log-Type": "Example",
"time-generated-field": "2022-02-17T11:25:40Z"
}

 

 

 

However when querying the table the TimeGenerated is reflecting the Ingestion time only. To confirm this I added a couple of properties to the JSON payload to include utcNow() as RunTime_t and the original event time as TimeStamp_s:

sirkillnotalot_0-1645108818915.png

 

I can work around it in the short-term by using a function to do something like the below. But would much rather preserve the original logs' TimeStamp.

 

Example
| extend TimeGenerated = TimeStamp_s
| where TimeGenerated <= ago(1h)

 


Have I missed something _really_ obvious here or this simply not working as intended?

 

References:

Azure Monitor HTTP Data Collector API - Azure Monitor | Microsoft Docs

Azure Log Analytics Data Collector - Connectors | Microsoft Docs

 

Edits: Added screenshot, formatting, references

3 Replies

@sirkillnotalot Looking at this documentation: Azure Log Analytics Data Collector - Connectors | Microsoft Docs  it states that the Time-generated-field "Represents the original date and time of the record." but it doesn't actually state that this is the TimeGenerated field (although it does lead one to think it would be).

 

Without adding any of the other date/time related fields, was there any other field that had a date/time in it?   Maybe something like TimeGenerated_t or TimeStamp_t?

@Gary Bushey 

The only fields that are date/time was the TimeGenerated passed in the original payload. As a bit of a test I stripped that field out using removeProperty(items('For_each'),'TimeGenerated') and used a static entry in the time-generated-field field instead:

sirkillnotalot_0-1645188926704.png

but all I get is:

sirkillnotalot_2-1645188998821.png

 

I initially thought the same as you and that the documentation may be misleading or incorrect but I've had a response from an MS support rep who've confirmed that the field is intended to preserve the record time stamp, not the ingested timestamp.

 

Think I might go create a new basic LogicApp and see if I can reproduce using a payload I control via PostMan and see if it's something in the way it processes records in a for_each loop.

 

OK so I've found the issue and posting resolution here in case anyone comes across this in the future.

 

The input we are receiving from our source system:

  {
    "type": "HTTP Outbound",
    "creator": "admin",
    "TimeGenerated": "2022-02-18 11:56:27",
    "table": "web_connections",
    "sys_id": "d2443d3c1b31451095a74195d34bcb65",
    "payload": {
      "hostname": "exampleurl.com",
      "sequence": "5310",
      "method": "GET",
      "url": "https://exampleurl.com",
      "response_status": "200",
      "source_table": "web_connections",
      "source_record": "2454c2271ba22c10fda632e8cd4bcb99",
      "transaction_name": "HTTP Outbound",
      "user_name": "root"
    }
  }

 

Issue 1

The connector doesn't like it if the payload with the date/time you're using is called TimeGenerated so I needed to:

  • add a new property with the TimeGenerated values:
    addProperty(triggerBody(),'TimeStamp',formatDateTime(triggerBody()?['TimeGenerated']))
  • to remove the timegenerated property:
    removeProperty(outputs('Compose'),'TimeGenerated')

sirkillnotalot_1-1645192683615.png

 

Issue 2

Usual formating mismatch so ran the formatdatetime() on the TimeGenerated field to convert to ISO8601 format

 

Issue 3

Probably a bit of a misunderstanding on my part - the 'time-generated-field' is looking for the property name, not the actual value.

sirkillnotalot_2-1645193058580.png

 

 

When it all works it'll populate the table using the TimeStamp field as both TimeGenerated, and TimeGenerated_s. The original TimeStamp property is actually removed.

sirkillnotalot_0-1645192250459.png

 

Interesting, and also frustratingly, despite the input for the 'time-generated-field' being wrong the API still returns "statusCode: 200" making you think everything is good to go.