Reply
Frequent Visitor
Posts: 4
Registered: ‎08-01-2018
Accepted Solution

SCCM solution template Malware table duplicate values

Hi,

I cannot use the SCCM solution template - when loading the data you can see many rows being pulled in, then all "load was cancelled by and error in loading a previous table" because

Malware

Column 'threatid' in Table 'Malware' contains a duplicate value '268478294' and this is not allowed for columns on the one side of a many-to-one relationshsip or for columns that are used as the primary key of a table

 

When checking this table, i do in fact see duplicate rows in the table, so could this be a bug in SCCM? I guess the import needs a select distinct?

 


Accepted Solutions
Regular Visitor
Posts: 30
Registered: ‎10-30-2015

Re: SCCM solution template Malware table duplicate values

@techs_uk, Microsoft employee here. Wanted to verify if the issue persists and we need to investigate this. From your message, it seems you are able to use the template without any issue now. Please let us know if that is not the case.

View solution in original post


All Replies
Regular Visitor
Posts: 30
Registered: ‎10-30-2015

Re: SCCM solution template Malware table duplicate values

Hello @techs_uk, are you still facing this issue or it has resolved by data refresh?

Frequent Visitor
Posts: 4
Registered: ‎08-01-2018

Re: SCCM solution template Malware table duplicate values

Are you getting the problem too?

I'm using it ok now. I've been off for two weeks and I can't remember it working though. I certainly looked at adjusting the queries for malware removing duplicates. cannot remember though! I don't think I would have touched the SQL table for SCCM.

Regular Visitor
Posts: 30
Registered: ‎10-30-2015

Re: SCCM solution template Malware table duplicate values

@techs_uk, Microsoft employee here. Wanted to verify if the issue persists and we need to investigate this. From your message, it seems you are able to use the template without any issue now. Please let us know if that is not the case.

Highlighted
Frequent Visitor
Posts: 4
Registered: ‎08-01-2018

Re: SCCM solution template Malware table duplicate values

I don't know if it was bad data in the cm_site DB, but my query for the malware in the report is now:

 

let
    Source = Sql.Database("vanguard", "PowerBI-SCCM"),
    pbist_MalwareView = Source{[Schema="pbist_sccm",Item="malware"]}[Data],
    #"Removed Duplicates" = Table.Distinct(pbist_MalwareView),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"threatid"})
in
    #"Removed Duplicates1"

 

After i told it to remove duplicates fo rthe threatid alone.

do you have the original query i can try again, to confirm that it was just my system for that time i tried it?