Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
QFE (Sorted for Sec Updates) to Neo4j with AutoIt
soDCmS7

The script creates each property as its own node, except 'Security Update' which is a shared node for all KBs with that description. Every KB has a relationship to the following KB, which with default sort keeps them all in order. The secondary relationship around a node that is widely common very much helped to declutter the result.

IDs are the KB# & row & column (row and column are from the array stringsplit returns).

Create
(KB289918912:Description{name: 'Update'}),
(KB289918913:HotFixID{name: 'KB2899189'}),
(KB289918914:InstalledBy{name: ' COMP_66GLL12_admin'}),
(KB289918915:InstalledOn{name: '8/8/2014 12:00:00 AM'}),
(KB269364322:Description{name: 'Update'}),
(KB269364323:HotFixID{name: 'KB2693643'}),
(KB269364324:InstalledBy{name: 'AECOMP_user1'}),
(KB269364325:InstalledOn{name: '10/13/2015 12:00:00 AM'}),
(KB289485232:Description{name: 'Security Update'}),
(KB289485233:HotFixID{name: 'KB2894852'}),
(KB289485234:InstalledBy{name: 'AECOMP_user1'}),
(KB289485235:InstalledOn{name: '9/11/2015 12:00:00 AM'}),
(KB289485643:HotFixID{name: 'KB2894856'}),
(KB289485644:InstalledBy{name: 'AECOMP_user1'}),
(KB289485645:InstalledOn{name: '9/11/2015 12:00:00 AM'}),
(KB291935553:HotFixID{name: 'KB2919355'}),
(KB291935554:InstalledBy{name: 'COMP_66GLL12_admin'}),
(KB291935555:InstalledOn{name: '3/18/2014 12:00:00 AM'}),
(KB291944263:HotFixID{name: 'KB2919442'}),
(KB291944264:InstalledBy{name: 'COMP_66GLL12_admin'}),
(KB291944265:InstalledOn{name: '3/18/2014 12:00:00 AM'}),
(KB292018973:HotFixID{name: 'KB2920189'}),
(KB292018974:InstalledBy{name: 'COMP_66GLL12_admin'}),
(KB292018975:InstalledOn{name: '8/7/2014 12:00:00 AM'}),
(KB293452083:HotFixID{name: 'KB2934520'}),
(KB293452084:InstalledBy{name: 'AECOMP_user1'}),
(KB293452085:InstalledOn{name: '9/11/2015 12:00:00 AM'}),
(KB293722093:HotFixID{name: 'KB2937220'}),
(KB293722094:InstalledBy{name: 'COMP_66GLL12_admin'}),
(KB293722095:InstalledOn{name: '3/18/2014 12:00:00 AM'}),
(KB2938772103:HotFixID{name: 'KB2938772'}),
(KB2938772104:InstalledBy{name: 'COMP_66GLL12_admin'}),
(KB2938772105:InstalledOn{name: '3/18/2014 12:00:00 AM'}),
(KB289918912)-[:Is_Description]->(KB289918913),
(KB289918913)-[:Is_InstalledBy]->(KB289918914),
(KB289918913)-[:Is_Followed_By]->(KB269364323),
(KB289918913)-[:Is_InstalledOn]->(KB289918915),
(KB269364322)-[:Is_Description]->(KB269364323),
(KB269364323)-[:Is_InstalledBy]->(KB269364324),
(KB269364323)-[:Is_Followed_By]->(KB289485233),
(KB269364323)-[:Is_InstalledOn]->(KB269364325),
(KB289485232)-[:Is_Description]->(KB289485233),
(KB289485233)-[:Is_InstalledBy]->(KB289485234),
(KB289485233)-[:Is_Followed_By]->(KB289485643),
(KB289485233)-[:Is_InstalledOn]->(KB289485235),
(KB289485232)-[:Is_Description]->(KB289485643),
(KB289485643)-[:Is_InstalledBy]->(KB289485644),
(KB289485643)-[:Is_Followed_By]->(KB291935553),
(KB289485643)-[:Is_InstalledOn]->(KB289485645),
(KB289485232)-[:Is_Description]->(KB291935553),
(KB291935553)-[:Is_InstalledBy]->(KB291935554),
(KB291935553)-[:Is_Followed_By]->(KB291944263),
(KB291935553)-[:Is_InstalledOn]->(KB291935555),
(KB289485232)-[:Is_Description]->(KB291944263),
(KB291944263)-[:Is_InstalledBy]->(KB291944264),
(KB291944263)-[:Is_Followed_By]->(KB292018973),
(KB291944263)-[:Is_InstalledOn]->(KB291944265),
(KB289485232)-[:Is_Description]->(KB292018973),
(KB292018973)-[:Is_InstalledBy]->(KB292018974),
(KB292018973)-[:Is_Followed_By]->(KB293452083),
(KB292018973)-[:Is_InstalledOn]->(KB292018975),
(KB289485232)-[:Is_Description]->(KB293452083),
(KB293452083)-[:Is_InstalledBy]->(KB293452084),
(KB293452083)-[:Is_Followed_By]->(KB293722093),
(KB293452083)-[:Is_InstalledOn]->(KB293452085),
(KB289485232)-[:Is_Description]->(KB293722093),
(KB293722093)-[:Is_InstalledBy]->(KB293722094),
(KB293722093)-[:Is_Followed_By]->(KB2938772103),
(KB293722093)-[:Is_InstalledOn]->(KB293722095),
(KB289485232)-[:Is_Description]->(KB2938772103),
(KB2938772103)-[:Is_InstalledBy]->(KB2938772104),
(KB2938772103)-[:Is_InstalledOn]->(KB2938772105)

This is the au3 that uses Powershell to gather QFE data and then the massaging done to build the Create Statement and Relationships. In the For..To loop at the bottom you will see I commented out the ubound and gave a max value of 10 (and used that value in line 198 'If $c = 5 AND $i < 10' to test $i for the max value), this is to provide the clearest picture, in production both of those values should be 'ubound() - 1'

Also pretty sure I have a bonus comma at the end (because i continue to play), change the stringtrimright to 2 if you are done adding items and simply want to fire the statement.

#include<array.au3>

$iPid = run('powershell Get-CimInstance -ClassName Win32_QuickFixEngineering -property * | Format-Table'  , @WindowsDir , @SW_HIDE , 0x2)

$sOutput = ""

 While 1
    $sOutput &= StdoutRead($iPID)
        If @error Then
            ExitLoop
        EndIf
 WEnd

;~ msgbox(0, 'string out' , $sOutput)

$aOutput = stringsplit($sOutput , @LF , 2)

;~ _ArrayDisplay($aOutput , "just split")

For $i = 0 to ubound($aOutput) - 1

$aOutput[$i] = stringregexpreplace(stringstripws($aOutput[$i] , 3) , "\s\s+" , ",")
$aOutput[$i] = stringreplace($aOutput[$i] , "_..." , ",")
$aOutput[$i] = stringreplace($aOutput[$i] , "\" , "_")
$aOutput[$i] = ",," & $aOutput[$i]

Next

_ArrayDelete($aOutput , 2)
_ArrayDelete($aOutput , 0)
;~ _ArrayDisplay($aOutput, "frmt list")

$aLine = stringsplit($aOutput[1] , "," , 2)

local $aFinal[0][ubound($aLine)]

;~ _ArrayDisplay($aFinal , "declared final")


for $i = 0 to ubound($aOutput) - 1

    If $aOutput[$i] = "" Then ContinueLoop

    If $i = 0 Then

        $aLine = stringsplit(stringtrimleft($aOutput[$i] , 1) , "," , 2)

    Else

        $aLine = stringsplit($aOutput[$i] , "," , 2)

    EndIf

;~      _ArrayDisplay($aLine)

        _ArrayTranspose($aLine)

;~      _ArrayDisplay($aLine , "Line Transposed")

        _ArrayConcatenate($aFinal , $aLine)

;~      _ArrayDisplay($aFinal , "Final with line added")



Next


;~ _ArrayDisplay($aFinal)

$sOutStr = ""
$sRelationships = ""
$SecUpdateFlag = 0

For $i = 1 to 10 ; ubound($aFinal) - 1
    For $c = 2 to ubound($aFinal , 2) - 1



    If $c = 2 And stringstripws($aFinal[$i][$c] , 3) = "Security Update" And $SecUpdateFlag = 0 Then

        $sOutStr &= "(" & $aFinal[$i][3] & $i & $c & ":" & $aFinal[0][$c] & "{name: '" & $aFinal[$i][$c] & "'})," & @LF
        $sSecUpdate =  $aFinal[$i][3] & $i & $c
        $SecUpdateFlag = 1
        $sRelationships &= "(" & $sSecUpdate & ")-[:Is_" & $aFinal[0][$c] & "]->(" & $aFinal[$i][3] & $i & $c + 1  & ")," & @LF

    ElseIf $c = 2 And stringstripws($aFinal[$i][$c] , 3) And $SecUpdateFlag = 1 Then

       $sRelationships &= "(" & $sSecUpdate & ")-[:Is_" & $aFinal[0][$c] & "]->(" & $aFinal[$i][3] & $i & $c + 1  & ")," & @LF

    ElseIf $c = 2 And stringstripws($aFinal[$i][$c] , 3) <> "Security Update" Then

       $sOutStr &= "(" & $aFinal[$i][3] & $i & $c & ":" & $aFinal[0][$c] & "{name: '" & $aFinal[$i][$c] & "'})," & @LF
       $sRelationships &= "(" & $aFinal[$i][3] & $i & $c & ")-[:Is_" & $aFinal[0][$c] & "]->(" & $aFinal[$i][3] & $i & $c + 1  & ")," & @LF

    Else

    $sOutStr &= "(" & $aFinal[$i][3] & $i & $c & ":" & $aFinal[0][$c] & "{name: '" & $aFinal[$i][$c] & "'})," & @LF

    If $c = 4 Then $sRelationships &= "(" & $aFinal[$i][3] & $i & $c - 1 & ")-[:Is_" & $aFinal[0][$c] & "]->(" & $aFinal[$i][3] & $i & $c & ")," & @LF
    If $c = 5 AND $i < 10 Then $sRelationships &= "(" & $aFinal[$i][3] & $i & $c - 2 & ")-[:Is_Followed_By]->(" & $aFinal[$i + 1][3] & $i + 1 & 3  & ")," & @LF
    If $c = 5 Then $sRelationships &= "(" & $aFinal[$i][3] & $i & $c - 2 & ")-[:Is_" & $aFinal[0][$c] & "]->(" & $aFinal[$i][3] & $i & $c  & ")," & @LF

    EndIf



    Next
Next

ClipPut("Create" & @LF & $sOutStr & stringtrimright($sRelationships , 1))
;~ ConsoleWrite("Create" & @LF & $sOutStr & stringtrimright($sRelationships , 1))

This is a work in progress, but wanted to beat the deadline.

@jexp

This comment has been minimized.

Copy link

@jexp jexp commented Jan 20, 2016

The deadline was extended, so you can continue to work on it, i.e. turn your import statement into a proper GraphGist statement (like in the original GraphGist) and also provide some use-case queries on top of that data.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment