Forum Discussion
CliveWatson
Microsoft
cyberHardik
To compare Syslog and CEF, you could join the past 2days with the previous 14days and compare them, this is an example
union Syslog, CommonSecurityLog
| where TimeGenerated between (startofday(ago(14d)) .. endofday(ago(3d)))
| summarize dcount(DeviceVendor), make_set(DeviceVendor) by Type
| join (
union Syslog, CommonSecurityLog
| where TimeGenerated > ago(2d)
| project TimeGenerated, Type, DeviceVendor
| summarize Twodays=dcount(DeviceVendor), make_set(DeviceVendor) by Type
) on $left.Type == $right.Type
| project-rename TwoWeeks = dcount_DeviceVendor
| extend weHaveLess = iif(Twodays < TwoWeeks,'We have less Vendors than before','')
| project-away Type1
Maybe in your reporting (run a new query in the Playbook) to show, the Sources connected over 14days and which are outside of the SLA. The Usage table (whilst having less data) is very fast as its aggregated already. Again and example you can build on, I switched to hours and only sources over 12hrs with no data, there is an SLA column to show those over 48hrs
Usage
| where TimeGenerated > startofday(ago(14d))
| summarize last_log = datetime_diff("hour", now(), max(TimeGenerated)),last_event_received = max(TimeGenerated) by TableName=DataType , Solution
| extend slaUnder2Days = iff(last_log <=48,"OK","SLA not ok")
| where last_log > 12
| order by last_log desc
cyberHardik
Jun 11, 2021Copper Contributor
I forget to mention that status of datatype also need to be fetched. whether they are connected or not . So I would fetch status of the datatype in tabular form. Please guide me as I am new to information security and less knowledge about KQL although I am enriching my knowledge day by day.
- CliveWatsonJun 11, 2021MicrosoftNot currently, for now, you could use a IIF to create you own status column, much like this example
| extend status_= iff(last_log <=48,"Connected","Not Connected, or no data sent in time period")